Run Excel Macro

[BurtArkin]

Using the code below, I create a temporary data file in the dblink file, then
open an excel file, fasb.xls.

DoCmd.OpenQuery "FASBMakeTempQry"
DoCmd.CopyObject "c:\dblink.mdb", , acTable, "FASBTableTemp"

Dim MyFile
MyFile = "C:\fasb.xls"
Me.Refresh
Dim x As Long
x = Shell("C:\Program Files\Microsoft Office\Office10\excel.exe" & " " &
Chr(34) & MyFile & Chr(34), 1)

What I want to do is then run an excel macro (that already exists in the
excel file) which would automatically import the data from the temporary
file. Can I do that? I'd appreciate all the help I can get. Thanks

 

[fredg]

Using the code below, I create a temporary data file in the dblink file, then
open an excel file, fasb.xls.

DoCmd.OpenQuery "FASBMakeTempQry"
DoCmd.CopyObject "c:\dblink.mdb", , acTable, "FASBTableTemp"

Dim MyFile
MyFile = "C:\fasb.xls"
Me.Refresh
Dim x As Long
x = Shell("C:\Program Files\Microsoft Office\Office10\excel.exe" & " " &
Chr(34) & MyFile & Chr(34), 1)

What I want to do is then run an excel macro (that already exists in the
excel file) which would automatically import the data from the temporary
file. Can I do that? I'd appreciate all the help I can get. Thanks

Why not just export the data directly from Access? No need to create a
new table. Just use a regular Select query, with or without criteria.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"QueryName", "c:\FolderName\SpreadsheetName.xls", True

It will export to an existing, or create a new, worksheet named
"QueryName", in the Workbook named "SpreadsheetName.xls".

 

[BurtArkin]

Thanks for your quick response.
Even if I were to do as you suggest, how do I cause an excel macro to be
implemented?

 

[Daniel Pineault]

If you still want to run the macro from access take a look at

http://www.cardaconsultants.com/en/msaccess.php?lang=en&id=0000000014#RunXlProc

 

[JonWayn]

In the Excel document, open the VBA window. Double-click the ThisWorkbook
item in the left pane. In the right pane, select the Workbook object from the
left combobox at the top, and the Open event of that object. From some point
within the body of this event, call the macro you need executed, or copy the
codes of that macro into the body of the Open event.

Regards