Running a macro from vba

  • Thread starter Thread starter Dan
  • Start date Start date
D

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.
 
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
 
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)
 
Bob,


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


Thanks
 
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.
 
Back
Top