Formulas From Access to Excel

[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

 

[Doug Bell]

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

Note " ' formula ' " single quotes

Doug Bell

 

[Guest]

Thanks for the reply Doug.

Unfortunately that didn't work.

I'll try a repost.

Brad