Formulas From Access to Excel

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Thanks for taking the time to read my question.

I have a query that selects data and dumps it into Excel.
For some of the fields I have a function that creates a
formula instead of a value because the user would like
the data that gets exported to be interactive.

The function and query work great, but when it gets
pasted into excel, the formulas just stay as text. You
have to select the formula in the formula bar, cut it
then paste it, then Excel recognizes it's a formula and
then it works perfectly.

Any ideas on how to get around this?

Thanks again,

Brad


CODE:
- - - - - - - - - - - - - - -

Function FormulaBuilder(TypeOfFormula As String, RowRef
As Integer, TypeOfProd As String) As String

If TypeOfFormula = "LC" Then
FormulaBuilder = "=IF(F" & RowRef & "=TRUE,D" &
RowRef & "*S" & RowRef & "+E" & RowRef & ",D" & RowRef
& "+E" & RowRef & ")"
GoTo TheEnd
End If

If TypeOfFormula = "LCUSD" Then
If TypeOfProd = "SS" Then
FormulaBuilder = "=IF(F" & RowRef & "=TRUE,D" &
RowRef & "+E" & RowRef & ",0)"
Else
FormulaBuilder = 0
GoTo TheEnd
End If
End If

If TypeOfFormula = "PricePerKgCAD" Then
FormulaBuilder = "=IF(T" & RowRef & "=""Ingred"",G" &
RowRef & "/100,0)"
GoTo TheEnd
End If

If TypeOfFormula = "MBSP" Then
FormulaBuilder = "=IF(K" & RowRef & "=0,0,(G" &
RowRef & "+J" & RowRef & "+K" & RowRef & ")/10)"
GoTo TheEnd
End If

If TypeOfFormula = "ABSP" Then
FormulaBuilder = "=IF(P" & RowRef & "=0,0,(G" &
RowRef & "+N" & RowRef & "+O" & RowRef & "+P" & RowRef
& "))"
GoTo TheEnd
End If

TheEnd:
Exit Function

End Function
 
Try:
FormulaBuilder = "'=IF(F" & RowRef & "=TRUE,D" &
RowRef & "*S" & RowRef & "+E" & RowRef & ",D" & RowRef
& "+E" & RowRef & ")'"

Note " ' formula ' " single quotes

Doug Bell
 
Back
Top