Send Formula to Excel From Access **REPOST**

  • 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

I received help from Doug. He suggested putting single
quotes inside the outer double quotes. Unfortunately
this didn't work.

Here is his example below:

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

Note " ' formula ' " single quotes

Doug
 
Do you have a handle into the Excel object such as Set objXL = CreateObject("Excel.Application")?

I think your best option is to format the cells that will contain formulas as "General" before you save the spreadsheet (but after the sheets are populated). That SHOULD fix the problem, if you can do it.

If you know in advance - or can find out - which columns the formulas will fall in, I'd suggest that you format ONLY those columns.
 
Answered in .externaldata. Please don't post the same message separately
to multiple groups: if you must post to more than one, crosspost.
 
Back
Top