Export To Excel

[Brad]

Thanks for taking the time to read my question.

I posted this in the Modules section, then noticed there
was an Importing, Exporting section. Sorry for the
double post.

Brad


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

 

[John Nurick]

Hi Brad,

I think the only way to fix this will be to use VBA Automation to do
something equivalent to the cut-and-paste you're doing manually.
Something like this untested air code:

Dim oXL As Excel.Application
Dim wbkW As Excel.Workbook
Dim wksS As Excel.WOrksheet
Dim C As Excel.Range

Set oXL = CreateObject("Excel.Application")
Set wbkW = oXL.Workbooks.Open("D:\Folder\NewlyExportedWorkbook.xls")
Set wksS = wbkW.Worksheets("TheSheet")

For Each C in wksS.UsedRange.Cells
If Left(C.Value, 1) = "=" Then
C.Formula = C.Value
End If
Next

Set wksS = Nothing
wbkW.Close True
Set wbkW = Nothing
With oXL.Workbooks
Do While .Count > 0
'make certain that no other workbooks are open, or Excel
'may not close (or maybe this is just superstition<g>)
.Item(.Count).Close False
Loop
End With
oXL.Quit
Set oXL = Nothing

 

[Guest]

I found a solution.

Export the file to a comma delimited format (.csv) Excel
will open it without the wizzard, and all the formulas
work.

Thanks to all of those that tried to help me with my
problem.

Brad