Solved: Macro Error when launching Excel as OLE Automation Object

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

CoupDeMistral

Thread Starter
Joined
Mar 23, 2010
Messages
2
Hi Folks

I have an Excel 2007 .XLSM file containing the following macro that performs a Fourier analysis

Code:
Sub Macro1()
    Application.Run "ATPVBAEN.XLAM!Fourier", ActiveSheet.Range("$C$1:$C$4"), ActiveSheet.Range("$E$1"), False, False
End Sub
The macro works fine when I launch the XLSM file manually. However, if I launch the file from a VB program (in Microsoft Access 2007), I get the following message:

'Cannot launch the macro ". The macro may not be available in this workbook or macros may be disabled'

The code I use to open the workbook and relevant add-ins:

Code:
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Open(MyPath & "Template1.xlsm")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISATPVBAEN.XLAM")
objXL.Workbooks.Open (objXL.Application.LibraryPath & "ANALYSISANALYS32.XLL")
objXL.RegisterXLL "Analys32.xll"
I then go to the worksheet and try to manually execute macro1 but I get the above error message. Note that:

  • "enable all macros" is set in Developer/Macro Security
  • "trust Access to the VBA etc." is turned on. in in Developer/Macro Security
  • I tried placing the macro in my PERSONAL.XLSB file to no avail (not sure this was worth it, but I found a suggestion to that effect on another forum)
  • I tried executing macro1 from the VBA program as follows but got the same error:

Code:
objXL.Run "Macro1"
or even:

Code:
objXL.Run "ATPVBAEN.XLAM!Fourier", objSheet.Range("$C$1:$C$4"), objSheet.Range("$E$1"), False, False
Again, the macro works just fine if I launch the file template1.xlsm manually

Finally, note that the error message does not refer to Macro1 itself, which is found and correctly launched by Excel, but to the Application.run command within the macro. That is the line of code where the debugger stops.

Any enlightened soul out there has any idea what I am doing wrong?

Thanks - Mike
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top