Running a macro from vba

[Dan]

Hi,

I am trying open an excel spreadsheet from access vba and
run the excel's macro, but I get on error on the part of
running the macro. Currently I have:

....
Dim exap As New Excel.Application
Dim exwb As Excel.Workbook
Dim exws As Excel.Worksheet

Dim excelbook As String
Dim path As String

excelbook = "Book1.xls"
path = CurrentProject.Path & "\" & excelbook


exap.Workbooks.Open path

Set exwb = exap.Workbooks(excelbook)
Set exws = exwb.ActiveSheet

exap.Run("Book1.xls!Macro1") '<----Can not execute this
line of code.

Set exws = Nothing
Set exwb = Nothing
Set exap = Nothing

Is there any other way to execute a macro from access?
I know that if I wanted to run the macro from excel vba
that line would be Application.Run("Book1.xls!Macro1").
Any help would be appreciated.

Thank you.

 

[NickHK]

Dan,
You need to create an instance of Excel to work with:
Set exap=New Excel.Application

set exwb=exap.Workbooks.Open (path)

Also your exws variable serves no purpose here.

NickHK

 

[Bob Phillips]

Nick,

Excel is instantiated with this statement

Dim exap As New Excel.Application

Dan,

I ran the code, from Excel admitted not Access, and it worked fine. What
error do you get?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dan]

Bob,


I get the error "There was an error executing the
command." And its on the Run part.


Thanks

 

[Dan]

I figured it out, the problem was that I needed to add
single quotes to the file name, example:
exap.Run("'Book1.xls'!Macro1")
- -

And that worked. I actually had to record a macro in
excel and see what code it generated.

Thank you very much.