How to get a FORMULA into Excel

[Phil Smith]

OK. I have a series of 48 queries, which I am exporting via
transferspreadsheet to a series of worksheets in four Excel workbooks.
(all version 2003)

Data fills column A through F.

I want column G to be blank, (easy,) and column H to be a formula which
multiplies G by F. This way, the end user can open the spreadsheet,
enter some numbers in column G, (like an order form) and get some totals.

Is this something which is doable in Access as part of the export, or
should I rely on a macro in Excel to add this data after the fact?

 

[Douglas J. Steele]

You'll need to do it via a macro in Excel.

Note that you can run the macro from Access.

 

[Phil Smith]

OK, Can you point me to a link on How do I do that? So far, I have not
even been able to run an Excel Macro from the commmand line. I can
build the macro easily enough, but how do I run it from Access?

 

[Ken Snell MVP]

'********************************
'* Call an EXCEL macro from ACCESS VBA *
'********************************

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub