Re: Run Excel macro from Access?

[Matthew Sullivan]

Public Sub RunExcelMacroOrSub()

'declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

'excel application stuff
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("YourFullFilePathNameHere")

'run the macro
xlApp.Run "YourMacroOrSubNameHere"

'save file
xlBook.Save

'done
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

 

[Richard Choate]

You can do that, but trust me, it doesn't work great. I used that method and
the macros didn't run like they should. If I opened Excel and ran the same
macros from within Excel, they worked great. But tell them to run from
Access and you get bad results. The Excel/Access combo is pretty much my
thing. I work with it all the time for my clients, and I'm telling you, you
will be better off just to put the code in Access instead of running an
Excel macro as shown below.
Richard Choate

Public Sub RunExcelMacroOrSub()

'declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

'excel application stuff
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("YourFullFilePathNameHere")

'run the macro
xlApp.Run "YourMacroOrSubNameHere"

'save file
xlBook.Save

'done
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing

End Sub