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

[JoeVegas]

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

 

[lameid]

You would open the appropriate workbook for automation and use the run method of the Excel application object.

 

[JoeVegas]

Are you referring to running a macro from within Excel ?
How do you execute a macro in an Excel workbook from MS Access 2003 ?

 

[lameid]

You may need to tweak this. You will have to set a reference to the Excel library.

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

 

[henniec]

In addition tow the code supplied by lameid you can replace
.Run ExcelMacro, optional_parameter_1, optional_parameter_2 ...

with objXL.Run "Macroname"

Hennie

 

[JoeVegas]

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 ?

 

[PHV]

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

 

[lameid]

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.

 

[JoeVegas]

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

 

[lameid]

You need to open the workbook programatically to use it... See my original code...

.Workbooks.Open FileName:= _
                strFilePath

 

[PHV]

Joe, please, read carefully the replies you've got ...

 

[JoeVegas]

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?

 

[lameid]

Yes.

On a line all by itself list the procedure name or funtion name with the appropriate parameters.

 

[JoeVegas]

Many thanks, again. I know I have a long way to go but this gives me a start to dig in much further.