Cannot run Excel Macro using Excel Application

[Ralph]

I have an Access module where I used the code below courtesy of "The Access
Web". It opens Excel OK, and brings up my file OK (I replaced the file
location below with my file address), but does not 'trigger' the Macro to
run. I replaced "AccountsViewEngine" with "Macro2" which is the name of my
Macro. I added "Debug.Print x" to the code and it shows -- Error 2015 when
ran. Also I do not have an automacro!
Any help would be greatly appreciated as usual -- Thanks!!!



'Code Courtesy of
'Dev Ashish

Sub sRunCARMa()
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
'Include CARMA in menu, run AutoOpen
.ActiveWorkbook.RunAutoMacros xlAutoOpen
x = .Run("AccountsViewEngine", 0)
Debug.Print x
End With
Set objXL = Nothing
End Sub

 

[Ken Snell [MVP]]

You cannot use the EXCEL intrinsic VBA constants from ACCESS VBA -- ACCESS
doesn't know what the values are, unless you set a reference to EXCEL in
your database (which we don't recommend that you do).

Instead, change
xlAutoOpen

to its actual value:
1

 

[Ralph]

Ken- Thanks for the reply - but it still doesn't work. X still shows --
Error 2015. I changed the code to look like this -- any other thoughts??

Sub sRunCARMa()
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
'Include CARMA in menu, run AutoOpen
.ActiveWorkbook.RunAutoMacros 1
x = .Run("AccountsViewEngine", 0)
Debug.Print x
End With
Set objXL = Nothing
End Sub

 

[Ken Snell [MVP]]

If you don't have an Automacro in your EXCEL file, then why are you trying
to run an automacro in the code? Meaning this line of code:
.ActiveWorkbook.RunAutoMacros 1

To run a specific macro that is in the workbook, use a line of code similar
to this:
objXL.Run .ActiveWorkbook.Name & "!" & "NameOfMacro"


Also, I would change this line of code
With objXL.Application

to this:
With objXL


--

Ken Snell
<MS ACCESS MVP>

 

[Ralph]

Ken -- Still stuck!! I'm (as you probably figured) a programming "wannabe".
I have read some of your other reply's to ques about controlling Excel data
from Access and think I have a "decent' understanding. I can make it do
quite a few things -- but - it will not run a macro. I tried the
suggestions you made (with dozens of attempts on the name and syntax) I also
tried --objXL.Application.ExecuteExcel4Macro ("!Macro2")
and objXL.ExecuteExcel4Macro ("!Macro2") -- (also with several attempts at
the name - I.E. not using the ! character , changing quotes etc. I
apologize for the
continued problems - but I'm pretty sure it can be done!!

 

[Ken Snell [MVP]]

It's possible that the ActiveWorkbook object is not working out for you in
this code (I don't like using Active... when automating EXCEL, as it's not
necessarily an object within the scope of the objects that I am declaring).
Let's try this:

objXL.Run "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS!Macro2"

--

Ken Snell
<MS ACCESS MVP>

 

[Ralph]

IT WORKS!!! Thanks so much for your patience on this!!
FYI ---
If I did not open the file first, I received the error message --
Canot find Macro "Bla - bla" Macro2
so -
I went ahead and opened the file, then simply used --
objXL.Run "Macro2"
Computers are SO DARN LITERAL!! I knew what I wanted it to do!!!
Thanks Again!!!!

 

[Ken Snell [MVP]]

I apologize that my suggestions were interpreted as meaning you did not need
to open the EXCEL file first. You do indeed need to open the file via
Automation and then run the macro.

Good luck.