Run Excel Macro through Access VBA

[Chris]

I have an Access macro which opens an Excel spreadsheet
and exports data to it. I am trying to then trigger a
macro in Excel through the VBA module in Access. I have
tried multiple approaches but keep getting object does not
support property errors. Any ideas?

 

[Ken Snell]

Here's some sample code for opening an EXCEL workbook and running a VBA
macro that's in the workbook in a public module:

Public Sub TestMacroRun()
Dim xlx As Object, xlw As Object
Set xlx = CreateObject("excel.application")
Set xlw = xlx.workbooks.Open("C:\Filename.xls")
xlx.Run "Filename.xls!MacroName()"
xlw.Close False
xlx.Quit
Set xlw = Nothing
Set xlx = Nothing
End Sub