Running Excel macro from access - code error

[geronimo_me]

Hi,

I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.

My code is:
Sub Run_Excel_Macro ()

Dim xls, xlWB As Object
Dim strFile, strMacro As String
strFile = "DIP_CTR.xl s"
strMacro = "DIP_CTR"
Set xls = CreateObject("E xcel.Applicatio n")
Set xlWB = xls.workbooks.O pen("G:\Hris\Ra chel\DIP STUFF\" &
strFile)
xls.Visible = True

xls.Run strMacro ****this is were the code errors******
Set xls = Nothing
Set xlWB = Nothing

End Sub

The macro runs perfect but my code errors at "xls.run.strmac ro even tho
the excel macro has run.

Any ideas would be greatly appreciated!

Flick. x

[Bruno Campanini]

<ge*********@ho tmail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi,

I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.

My code is:
Sub Run_Excel_Macro ()

Dim xls, xlWB As Object
Dim strFile, strMacro As String
I suppose you are not aware that writing
Dim strFile, strMacro As String
is the same as writing
Dim strFile as Variant, strMacro as String

If you want both vars be defined as string you must write
Dim strFile As String, strMacro As String

Bruno

strFile = "DIP_CTR.xl s"
strMacro = "DIP_CTR"
Set xls = CreateObject("E xcel.Applicatio n")
Set xlWB = xls.workbooks.O pen("G:\Hris\Ra chel\DIP STUFF\" &
strFile)
xls.Visible = True

xls.Run strMacro ****this is were the code errors******
Set xls = Nothing
Set xlWB = Nothing

End Sub

The macro runs perfect but my code errors at "xls.run.strmac ro even tho
the excel macro has run.

Any ideas would be greatly appreciated!

Flick. x

[David Lloyd]

I have run your code in Access changing only the strFile and strMacro and it
runs without error. This is with Office 2003. You may want to supply more
information (Access and Excel version, etc.).

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

This response is supplied "as is" without any representations or warranties.
<ge*********@ho tmail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .
Hi,

I am trying to run an Excel macro from an Access module, however when I
run the code the macro runs but then I get an error in Access. The
error is: Run-time error "440", Automation error.

My code is:
Sub Run_Excel_Macro ()

Dim xls, xlWB As Object
Dim strFile, strMacro As String
strFile = "DIP_CTR.xl s"
strMacro = "DIP_CTR"
Set xls = CreateObject("E xcel.Applicatio n")
Set xlWB = xls.workbooks.O pen("G:\Hris\Ra chel\DIP STUFF\" &
strFile)
xls.Visible = True

xls.Run strMacro ****this is were the code errors******
Set xls = Nothing
Set xlWB = Nothing

End Sub

The macro runs perfect but my code errors at "xls.run.strmac ro even tho
the excel macro has run.

Any ideas would be greatly appreciated!

Flick. x

[geronimo_me]

Excel 2000 and Access 2000.

Many Thanks

[geronimo_me]

My macro will run in Excel (xls. macro works fine) however my code in
Access debugs with the same error msg "Run-time error "440", Automation
error." (as soon as macro has finished in xls)

Please help - I relly don't get why the macro runs fine from Access but
then errors in Access

Flick. x

[RoyVidar]

Hi!

In addition to the string declarations, mentioned
by Bruno, the same is true for the object
declarations.

Dim xls, xlWB As Object

Declares xls as variant, and xlwb as object, try:

Dim xls as object, xlWB As Object

I can't find any direct errors, but perhaps the
Excel macro isn't finished at the time it hits
the lines releasing the object variables?
Try using a DoEvents:

xls.Run strMacro
DoEvents
Set xlWB = Nothing
Set xls = Nothing

And try reversing the order of releasing
(workbook first).

I don't know, but perhaps also experiment a little
with were you place the xls.visible line?

If neither of these works, I think I'd consider
doing the whole operation from Access. I e copy all
the code from the macro into Access, prefix all
objects, properties and methods with the appropriate
object variables, and see if that works better.

Roy-Vidar

geronimo...@hot mail.com wrote:

My macro will run in Excel (xls. macro works fine) however my code in
Access debugs with the same error msg "Run-time error "440", Automation error." (as soon as macro has finished in xls)

Please help - I relly don't get why the macro runs fine from Access but then errors in Access

Flick. x

[geronimo_me]

Thank you.