Using Access to open and run a macro in Excel

[Guest]

Hello,
I am looking for help on how to open a specfic excel file and run a
"refresh" macro in Excel from an Access database.
I was able to use the "RunCmd" function to open the specific excel
worksheet, but I would like excel to run a "refresh" macro automatically on
open. My excel file runs queries from my Access database.

 

[Van T. Dinh]

See Microsoft Support Knowledge Base article:

http://support.microsoft.com/kb/177760

Check Excel VB Help on the Workbook_Open Event also.

 

[Ron2006]

Per Van's reply, Here is the code if the macro is in the workbook that
you are opening:


Sub XLTest()
Dim XL as Object

Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open "C:\My Documents\ExcelFile.xls"

' If there is more than one macro called TestMacro,
' the module name would be required as in
'
' XL.Run "Module1.TestMacro"
'
' to differentiate which routine is being called.
'
XL.Run "TestMacro"

End Sub


Now if the macro is in a different "macro.xls" workbook then

Sub XLTest()
Dim XL as Object

Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open "C:\My Documents\Macros.xls"

XL.Workbooks.Open "C:\My Documents\ExcelFile.xls"

' If there is more than one macro called TestMacro,
' the module name would be required as in
'
' XL.Run "Module1.TestMacro"
'
' to differentiate which routine is being called.
'
XL.Run "Macros.xls!TestMacro"

End Sub

Basically you have to tell it where the macro is. And you may have to
be sure which workbook and which tab you are sitting on, depending on
how the macro is written.

Ron