Need to run an Excel macro from Access

[Guest]

Good day Everybody,

I am working on a database for my CFO. A section of this database exports
data from specific queries and exports them to Excel spreadsheets. I then
need to be able to run an Excel Macro automatically on that data so a user
doesn't need to tell Excel to run a macro.

Can anybody suggest a macro (or preferably VBA code) that can execute an
Excel macro from Access?

 

[Ken Snell [MVP]]

This may get you started:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing

 

[Guest]

Ken,

Thanks.

I'll see if I can implement your suggestion correctly. I am familiar w/
programming concepts but a novice in regards to VBA syntax and commands. I'll
let u know if I need further assistance.

Thanks again.

 

[Guest]

This all makes sense, but when it runs, it opens the correct workbook, but an
error occurs, saying that the "macro 'NEW INTERBOARD.xls!mainformat' cannot
be found"
The mainformat macro is a general module.
I've tried making it private sub, and also making it a workbook macro, but
nothing has worked.
Any help would be appreciated.

here's the syntax in access:


Private Sub Label4_Click()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "NEW INTERBOARD.xls"
strMacro = "mainformat"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("U:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing

 

[Guest]

I got it to work. It's the same syntax, but it works now. Not sure how.
One thing that is puzzling:
In my excel macro, it goes back and forth between IE and excel, so when it
needs to switch back to excel, I have "Appactivate "Microsoft Excel".
It works when the macro is just run from excel, but when I run it from the
access VB, it gives me a "invalid call or argument" error.
I'm guessing it has to do with the fact that under this Access macro, the
excel sheet is not opened under the typical "Microsoft Excel" object type.

It's not crucial at all to have excel activated when the code runs, as the
data is still formatted ok in excel without it being visible, I was just
wondering why this would occur.

Thanks