Run excel macro via Access - macro runs, then errors in Access

[geronimo_me]

Hi,

I have the following code in an access module:

Sub Run_Excel_Macro()
Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "DIP_CTR.xls"
strMacro = "DIP_CTR"

Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" &
strFile)
xls.Visible = True

'Run Macro
xls.Run strMacro

Set xls = Nothing
Set xlWB = Nothing

End Sub

The excel workbook opens and the macro runs, then i get a runtime error
in access "run error 440" automation error

Any ideas - many thanks

Flick. x

[David Lloyd]

One possibility is that if the macro is in the workbook, then call the Run
function on the workbook instead of the Excel application.

For example:

xlWB.Application.Run strMacro

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ge*********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi,

I have the following code in an access module:

Sub Run_Excel_Macro()
Dim xls, xlWB As Object
Dim strFile, strMacro As String

strFile = "DIP_CTR.xls"
strMacro = "DIP_CTR"

Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open("G:\Hris\Rachel\DIP STUFF\" &
strFile)
xls.Visible = True

'Run Macro
xls.Run strMacro

Set xls = Nothing
Set xlWB = Nothing

End Sub

The excel workbook opens and the macro runs, then i get a runtime error
in access "run error 440" automation error

Any ideas - many thanks

Flick. x

[geronimo_me]

I still get the same run time error when i replace "xls.Run strMacro"
with "xlWB.Application.Run strMacro"