In Access Can't Run Excel Macro Through Automation

[scott w t]

using access 2007 and excel 2007, can't run a macro in excel from access

here is the error message: "The macro may not be available in this workbook
or all macros may be disabled."

the workbook that contains the macro is in a location that is trusted by
both access and excel

here's the code:
module level declaration
Private ExcApp As New Excel.Application
procedure code
the following works
ExcApp.Visible = True
ExcApp.Workbooks.Open FileSpecification
(the AutoOpen macro runs ok here)
ret = ShowWindow(ExcApp.hwnd, SW_RESTORE)
ret = MoveWindow(ExcApp.hwnd, L, T, R, B, True)
this doesn't work (see error message above)
ExcApp.Run "MacroName"

can anybody help?

 

[Peter T]

Prefix MacroName with filename and an exclamation mark. Depending on the
file name you might also need to embrace it with apostrophes, best to
include them to be on the safe side, eg

sMacro = "'" & sWBname & "'" & "!" & "MacroName"
ExcApp.Run sMacro

where sWBname is the workbook name excluding path

Regards,
Peter T

 

[scott w t]

Hi Peter. Thanks. I tried your suggestion both with and without specifiying
the path, but no luck. Here's my slightly simplified code with some extra
spaces that aren't really there for readability:

Dim str_Macro As String
str_Macro = " 'FileName.xlsm' " & "!" & " 'MacroName' "
ExcelApplication.Run str_Macro

Did I execute your suggestion as intended? Thanks.

 

[Peter T]

MacroName should NOT be embraced with the apostrophes, only the filename and
then only then necessary depending on what characters it contains, spaces &
certain punctuation (no harm to include the apostrophes around the filename
if not required).

Looks like you are using XL2007 which I don't have so perhaps there's
something else you need to do.

Save a workbook named (say) Book1.xls with a macro named say Test

Sub Test()
Msgbox "hello"
End sub

Save the workbook, close and reopen it

In another workbook

Sub RunTest
dim sMacro as string

sMacro = "Book1.xls!Test"

Application.Run sMacro

End Sub

Normally that should work, and with a filename like that without the
apostrophes, at least it should in any pre-2007 version of XL

Assuming it does, close Book1.xls.
Within Excel, automate a new instance of XL, make it visible, load Book1.xls
(with code) and xlApp.Run the macro in the other instance. If necessary,
first bring the other instance to the foreground so you can see the msgbox.

Regards,
Peter T

 

[scott w t]

Thanks again, Peter. I tried what you recommended but it didn't work.
However, it inspired me to go through the Excel macro and comment everything
out and then add it back in line by line and access it from Access through
automation to see where it got hung up. And I found it. I had a macro and a
range with the same name.

Happy new year, Scott

 

[Peter T]

And I found it. I had a macro and a

range with the same name.

Ah, that old chestnut. I should have thought of that!

Regards,
Peter T

Thanks again, Peter. I tried what you recommended but it didn't work.
However, it inspired me to go through the Excel macro and comment everything
out and then add it back in line by line and access it from Access through
automation to see where it got hung up. And I found it. I had a macro and a
range with the same name.

Happy new year, Scott

<snip>