Running a macro in 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.
 
In VBA, you could do something similar to this:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "Ken.xls"
strMacro = "Testing1"
' Export the table
DoCmd.TransferSpreadsheet acExportDelim, , "TableName", "C:\" & strFile
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub


I think you're using ( ) after the Run method and you don't use them there
because you're calling the method, not setting something equal to it.
 
Back
Top