Excel automation

[Guest]

I've written the following code to open an Excel workbook and run a macro:

Sub Run_Excel_Macro(f As Variant, m As String) ' f is the Excel filename
and m is the macro
Dim objXL As Object, X, nf
Set objXL = CreateObject("Excel.Application")
DoEvents
With objXL.Application
.Visible = True
.Workbooks.Open (f)
X = Run(m)
objXL.DisplayAlerts = False
objXL.Quit
End With
Set objXL = Nothing
End Sub

It works fine but I only seem to be able to run it once per execution of the
Access application. If I try to run it a 2nd time, the Excel workbook opens
but then I get an error in the Access application. If I close the Access app
and re-open it, I can run it again.

Am I leaving something open? Do I have to do something else after closing
the Excel workbook?

I will appreciate any help I can get.

Miguel

 

[David Lloyd]

Miguel:

One suggestion would be to call the Run method on the Workbook object rather
than the Excel.Application object. This assumes the macro is in the
workbook you are opening. For example:

Sub Run_Excel_Macro(f As Variant, m As String) ' f is the Excel filename
and m is the macro
Dim objXL As Object, X, nf
Dim objwkb As Object
Set objXL = CreateObject("Excel.Application")
DoEvents
With objXL.Application
.Visible = True
Set objwkb = .Workbooks.Open(f)
X = objwkb.Application.Run(m)
objXL.DisplayAlerts = False
objXL.Quit
End With
Set objwkb = Nothing
Set objXL = Nothing
End Sub

If that does not solve the issue, you may want to give the details of the
error message you are receiving.

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

This response is supplied "as is" without any representations or warranties.


message I've written the following code to open an Excel workbook and run a macro:

Sub Run_Excel_Macro(f As Variant, m As String) ' f is the Excel filename
and m is the macro
Dim objXL As Object, X, nf
Set objXL = CreateObject("Excel.Application")
DoEvents
With objXL.Application
.Visible = True
.Workbooks.Open (f)
X = Run(m)
objXL.DisplayAlerts = False
objXL.Quit
End With
Set objXL = Nothing
End Sub

It works fine but I only seem to be able to run it once per execution of the
Access application. If I try to run it a 2nd time, the Excel workbook opens
but then I get an error in the Access application. If I close the Access app
and re-open it, I can run it again.

Am I leaving something open? Do I have to do something else after closing
the Excel workbook?

I will appreciate any help I can get.

Miguel

 

[Guest]

I think what is happening is you are creating an additional instance of Excel
without knowing you are dong it. Your Quit will stop the instance you
create, but if an instance is created by Access, it does not stop. You can
tell this for sure by opening Task Manager, selecting the Processes tab and
looking for Excel.
What makes me think this is experience with this problem and the way you are
referencing the objects. You might try modifying your code a little:

Sub Run_Excel_Macro(f As Variant, m As String) ' f is the Excel filename
and m is the macro
Dim objXL As Object, X, nf
'What data type do you expect x and nf to be?
'In this case, the are both Variant. That is because you do not specifiy a
type. Each item must have it's own typing, so if you want them to be String
(for example), it would need to be:
Dim objXL As Object, X as String, nf as String
Also meaningful names are helpful/
Dim objwkb As Object
Set objXL = CreateObject("Excel.Application")
DoEvents
With objXL.Application
.Visible = True
Set objwkb = .Workbooks.Open(f)
X = .Run(m) <-- I think this may have been the problem
.DisplayAlerts = False
.Quit
End With
Set objwkb = Nothing
Set objXL = Nothing
End Sub