from access using automation run excel macro

[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?

 

[Stuart McCall]

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?

This is a guess. Does the Excel project compile? (Menu item Debug -> Compile
Project in Excel's VBE) If it doesn't, that may be causing the 'all macros
may be disabled' bit because VBA will consider the project code to be
invalid.

 

[scott w t]

Thanks Stuart. The code modules compile and the macro runs without error
when accessed directly in Excel. Any other ideas out there?