Very simple Excel automation script not closing Excel - help!

[MrBoom]

I'm in the process of writing an Access application that performs some
Excel automation. I've been having problems with the Excel process
continuing to run upon script termination and error 462 problems. I
gather both are associated with making implicit references rather than
explicit references to objects. I've read much about making explicit
references in my automation script both on here and in the MSKB.

I was pretty sure I'd got all my references correct, but Excel still
wasn't closing. Just to test it, I created a new script that simply
opens the references then closes them again. Guess what, it suffers
with the same problem. I've pasted the script below, can anyone else
see what's wrong with it because I can't!!

Thanks.

Private Sub Form_Load()

Dim appExcel As Excel.Application ' I've also tried "as Object"
here
Dim wbExcel As Excel.Workbook
Dim wsExcel As Excel.Worksheet

Set appExcel = Excel.Application
Set wbExcel = appExcel.Workbooks.Add
Set wsExcel = wbExcel.Sheets(1)

Set wsExcel = Nothing
wbExcel.Close SaveChanges:=True
Set wbExcel = Nothing
appExcel.Quit
Set appExcel = Nothing

End Sub

 

[Rick Stebbins]

Try setting your objects to Nothing at the end of the
script. Also, when instantiating your appExcel object,
trying using either GetObject (to capture an open instance
of Excel) or New Excel.Application (to create a new
instance). For example:

www.mvps.org/word/FAQs/InterDev/ControlXLFromWord.htm

Errors such as 462 - 'The remote server machine does not
exist or is unavailable' can occur if Excel quits or
crashes, or if Excel is waiting for the user to respond to
a dialog box. I usually go to a lot of effort to trap
error messages, turn off or avoid prompts, and check that
my Excel object variable is still instantiated. Some
common prompts are for error alerts, passwords, MS
Installer, file conversion, etc.

 

[Cindy Meister -WordMVP-]

Hi MrBoom,

One thing you're not showing us here is how you're actually starting up
/ connecting to the Excel.Application. You DIM and you SET, but nowhere
do I see a GetObject, CreateObject, or New...

I'm in the process of writing an Access application that performs some
Excel automation. I've been having problems with the Excel process
continuing to run upon script termination and error 462 problems. I
gather both are associated with making implicit references rather than
explicit references to objects. I've read much about making explicit
references in my automation script both on here and in the MSKB.

I was pretty sure I'd got all my references correct, but Excel still
wasn't closing. Just to test it, I created a new script that simply
opens the references then closes them again. Guess what, it suffers
with the same problem. I've pasted the script below, can anyone else
see what's wrong with it because I can't!!

Thanks.

Private Sub Form_Load()

Dim appExcel As Excel.Application ' I've also tried "as Object"
here
Dim wbExcel As Excel.Workbook
Dim wsExcel As Excel.Worksheet

Set appExcel = Excel.Application
Set wbExcel = appExcel.Workbooks.Add
Set wsExcel = wbExcel.Sheets(1)

Set wsExcel = Nothing
wbExcel.Close SaveChanges:=True
Set wbExcel = Nothing
appExcel.Quit
Set appExcel = Nothing

End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Jason]

You need to set an excel object using GetObject or CreateObject. You
can try replacing your "Set appExcel = Excel.Application" line with
the following:

On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = True
Err = 0
End If
On Error GoTo 0

You will need to make sure that you've referred to the "Microsoft
Excel X.X Object Library" under Tools\References. Also, you'll need
to modify the workbook in order for it to have anything to save.

Jason Goto
AnalysisWorks Inc
Evidence-Based Management Consulting
www.analysisworks.net