Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I call/execute an Excel macro from Access 2003 3

Status
Not open for further replies.

JoeVegas

Programmer
Oct 3, 2001
53
US
How do I call/execute an Excel macro from Access 2003. TIA
 
You would open the appropriate workbook for automation and use the run method of the Excel application object.
 
Are you referring to running a macro from within Excel ?
How do you execute a macro in an Excel workbook from MS Access 2003 ?
 
You may need to tweak this. You will have to set a reference to the Excel library.

Code:
Dim XLAPP As Excel.Application
Set XLAPP = CreateObject("Excel.Application")
     With XLAPP
            '.Visible = True  'When you debug, sometimes it helps to see what is going on
            .Workbooks.Open FileName:= _
                strFilePath
         [red].Run ExcelMacro, optional_parameter_1, optional_parameter_2 ... [/red]
         .ActiveWorkbook.Close (True) 'Close and save workbook
         .Quit
     End With
Set XLAPP = Nothing
 
In addition tow the code supplied by lameid you can replace
.Run ExcelMacro, optional_parameter_1, optional_parameter_2 ...

with objXL.Run "Macroname"

Hennie
 
I get "Runtime error "424" Object Required" stepping through the below code:

Sub CallExcelMacro()

'Declare an object variable
Dim xlApp As Excel.Application
'Declare an instance of Excel
Set xlApp = New Excel.Application
With xlApp
'See Excel workbook/worksheet after run
xlApp.Visible = True
'Execute excel macro
objXL.Run ExcelMacro "TSBTestDataA"
'Close and save workbook
.ActiveWorkbook.Close (True)
End With
End Sub

Is it necessary to have the Excel workbook open when I call the macro from the Access module ?
 
Is it necessary to have the Excel workbook open when I call the macro
Yes, look at the Open method of the Workbooks class.

Furthermore, replace this:
objXL.Run ExcelMacro "TSBTestDataA"
with either this:
xlApp.Run ExcelMacro "TSBTestDataA"
or this:
.Run ExcelMacro "TSBTestDataA"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
In addition to PHV's comments, in my Example ExcelMacro should be replaced with the Excel macro / procedure name or used as a variable for it.
 
I now get "Runtime 1004 Methor 'Run' of object '_Application' failed,

Sub CallExcelMacro()

'Declare an object variable
Dim xlApp As Excel.Application
'Declare an instance of Excel
Set xlApp = New Excel.Application
With xlApp
'See Excel workbook/worksheet after run
xlApp.Visible = True
'Execute excel macro
xlApp.Run "TSBTestDataA" 'runtime error 1004
'Close and save workbook
.ActiveWorkbook.Close (True)
End With
End Sub
 
You need to open the workbook programatically to use it... See my original code...

Code:
.Workbooks.Open FileName:= _
                strFilePath
 
Joe, please, read carefully the replies you've got ...
 
PHV, lameid: Many thanks. With your help I have successfully used application automation between Access and Excel for the 'first' time. - Is it possible to call another macro, in the same workbook, from within a macro?
 
Yes.

On a line all by itself list the procedure name or funtion name with the appropriate parameters.
 
Many thanks, again. I know I have a long way to go but this gives me a start to dig in much further.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top