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

 

[trevorC via AccessMonster.com]

Here's a start, some modified code for you to try...

Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Data_Range
Dim Worksheet_Name
Dim T, tt, gg
On Error GoTo get_excel_file__Err
T = DLookup("[email file location]", "[customer names]", "forms![get
external data]![customer name]=[customer name]")
tt = Me.Delivery_Docket__ + ".xls" ' InputBox("Enter the FULL drive
and path to the file ", "User input required")
Worksheet_Name = "sheet1"
gg = T + tt
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Workbook.Worksheets(1).Name = "sheet1"
Set Current_Worksheet = Excel_Workbook.Worksheets(Worksheet_Name)
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True

' heres ware you update the spread sheet...

Current_Worksheet.Range("A1").FormulaR1C1 = "barcode"
Current_Worksheet.Range("B1").FormulaR1C1 = "description"
Current_Worksheet.Range("C1").FormulaR1C1 = "part number"

Excel_Workbook.Save
Excel_Application.Quit

All done from within Access