Running Excel Macro from Access

[clifftech]

Please help.

I am trying to run an Excel macro from an Access command button with this code:

Private Sub Run_xlMacro_Click()

Dim objXLApp As Excel.Application
Dim objXLWB As Excel.Workbook
Dim strMacro As String

Set objXLApp = CreateObject("Excel.Application")

Set objXLWB = GetObject("S:\Projects\template.xlsm")

objXLApp.Run ("template.xlsm!msgtest")

objXLApp.Quit

End Sub

I'm getting the error - Method 'Run' of object'_Application'failed.


I'm using Office 2007 and for references in Access have "Microsoft Excel 12.0 Object Library" checked. The macro in Excel is for bring up a simple message box. What am I leaving out?

 

[PHV]

Replace this:
Set objXLWB = GetObject("S:\Projects\template.xlsm")
with this:
objXLApp.Workbooks.Open "S:\Projects\template.xlsm"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[clifftech]

worked great. thanks.