Application.Quit issue in Excel 2003

[John Winterbottom]

(On WinXP Pro SP2 / Access 2003 / Excel 2003)
The following code, when run from Access, leaves a copy of Excel.exe hanging
around in memory.

'***********************************************
Sub testXL()

Dim objXL As Object
Dim wkbXL As Object
Dim strFileName As String

On Error GoTo errHandler
strFileName = "C:\Test.xls"
Set objXL = CreateObject("Excel.Application")
Set wkbXL = objXL.Workbooks.Open(strFileName)

exitHere:
On Error Resume Next
wkbXL.saved = True
wkbXL.Close

' also tried this
' wkbXL.Close savechanges:=False

Set wkbXL = Nothing
objXL.Quit
Set objXL = Nothing
On Error GoTo 0
Exit Sub

errHandler:
MsgBox (Err.Description)
Resume exitHere

End Sub
'***********************************************

Code works fine against Excel 2002 and 2000. Is this a known issue with
Excel 2003? I've tried many variants including explicitly referencing Excel
library, early binding, explicit Save of workbook. None of them have worked.
I tried setting Application.Visible=True to see if there was any dialog open
that was waiting for input but nothing showed up.

Any help would be appreciated.

 

[RWN]

John;
This will be of no help (just empathy!) but I have the exact same
problem calling Excel from Word.
It simply refuses to kill the instance.
(My macro gets itself very confused because I have to determine if there
already an instance of Excel because I don't want to shut down the user
if they already have Excel open. Of course, after the 1st time, it
thinks there is an instance running.)

 

[Jim Cone]

John and Rob,

Maybe this will help...
'-------------------------------------------------------------
Here are some general guidelines to use when automating Excel...
'Jim Cone - San Francisco, CA

1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=True 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.

'------------------------------------------------------------

Articles dealing with unqualified references and automation
application not quitting.

1. 178510 - PRB: Excel Automation Fails Second Time Code Runs
http://support.microsoft.com/default.aspx?scid=kb;en-us;178510
Summary: While running code that uses Automation to control Microsoft Excel,
one of the following errors may occur: With Microsoft Excel 97 and later, you receive the error:
Run-time error '1004': Method '<name of method>' of object '_Global' failed -or-...

2. 189618 - PRB: Automation Error Calling Unqualified Method or Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;189618
Summary: While running code that uses Automation to control Microsoft Word 97, Word 2000, or Word 2002,
you may receive one of the following error messages:
Run-time error '-2147023174' (800706ba) Automation error -or- Run-time error '462': The remote server...

3. 199219 - XL2000: Automation Doesn't Release Excel Object from Memory
http://support.microsoft.com/default.aspx?scid=KB;en-us;q199219
When you run a macro that uses automation to create a Microsoft Excel object (instance),
the Excel object does not exit from memory when you specify.
If you create another Excel object after quitting the first, a second instance is in memory.
This problem occurs when your macro uses a "WITH" statement that refers to the automation object.

4. 319832 - INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;319832
Summary: When you automate a Microsoft Office application,
you may receive an error message or you may experience unexpected behavior, as follows.
You may receive one of the following error messages: Error 91: Object variable or With block variable not set....

5. 317109 - Visual Basic .Net & Visual C#

'-------------------------------------------------------------
Regards,
Jim Cone
San Francisco, CA

 

[RWN]

Jim;
Thank you very much.
I'm getting items 1->3 in the KB list.

Looking forward to work tomorrow (which is unusual!) to try it out, will
post back.

Again, thanks.
--
Regards;
Rob
------------------------------------------------------------------------

John and Rob,

Maybe this will help...
'-------------------------------------------------------------
Here are some general guidelines to use when automating Excel...
'Jim Cone - San Francisco, CA

1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=True 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.

'------------------------------------------------------------

Articles dealing with unqualified references and automation
application not quitting.

1. 178510 - PRB: Excel Automation Fails Second Time Code Runs
http://support.microsoft.com/default.aspx?scid=kb;en-us;178510
Summary: While running code that uses Automation to control Microsoft Excel,
one of the following errors may occur: With Microsoft Excel 97 and later, you receive the error:
Run-time error '1004': Method '<name of method>' of object '_Global' failed -or-...

2. 189618 - PRB: Automation Error Calling Unqualified Method or Property
http://support.microsoft.com/default.aspx?scid=kb;en-us;189618
Summary: While running code that uses Automation to control

Microsoft Word 97, Word 2000, or Word 2002,

you may receive one of the following error messages:
Run-time error '-2147023174' (800706ba) Automation error -or-

Run-time error '462': The remote server...

3. 199219 - XL2000: Automation Doesn't Release Excel Object from Memory
http://support.microsoft.com/default.aspx?scid=KB;en-us;q199219
When you run a macro that uses automation to create a Microsoft Excel object (instance),
the Excel object does not exit from memory when you specify.
If you create another Excel object after quitting the first, a second instance is in memory.
This problem occurs when your macro uses a "WITH" statement that

refers to the automation object.

4. 319832 - INFO: Error or Unexpected Behavior with Office Automation

When You Use Early Binding in Visual Basic

http://support.microsoft.com/default.aspx?scid=kb;en-us;319832
Summary: When you automate a Microsoft Office application,
you may receive an error message or you may experience unexpected behavior, as follows.
You may receive one of the following error messages: Error 91:

Object variable or With block variable not set....

 

[RWN]

Jim;
That did it!
Had everything set up as it should be EXCEPT my range statement.
Had XLSht.Range(Cells(r,c),Cells(r,c)). As soon as I added the "XLSht." ref.
to both "Cells" all was well.
Didn't realize that I needed the sheet reference on the "Cells" items.

Thank you (you saved my hair!)

 

[Jim Cone]

Rob,
You are welcome.
Feedback, on offered solutions, is always appreciated.
Regards,
Jim Cone
San Francisco, CA

Jim;
That did it!
Had everything set up as it should be EXCEPT my range statement.
Had XLSht.Range(Cells(r,c),Cells(r,c)). As soon as I added the "XLSht." ref.
to both "Cells" all was well.
Didn't realize that I needed the sheet reference on the "Cells" items.
Thank you (you saved my hair!)

Regards
Rob

-snip-

 

[John Winterbottom]

John and Rob,

Maybe this will help...
'-------------------------------------------------------------
Here are some general guidelines to use when automating Excel...
'Jim Cone - San Francisco, CA

Thsnks for the info Jim. I've in fact seen these already and they don't
apply to my situation. As I said, the code I am using is very simple - no
With statements, no implict object references, clean up correctly etc. -
nothing works. This is code that has been running for a few years with no
problems against previous versions of Excel - it's only with 2003 that I
have the issue.

I can only assume this is a bug so I'll just use a brute-force kill and
leave it at that.

 

[RWN]

John;
Using WinXPPro SP2/Office'03
Tried your logic in Access'03 and it worked as it should.
Made the app visible and inserted breakpoints, just to be sure.
Excel opened up as it should and died as soon as the objXL object went out
of scope (set to nothing).
Viewed the task manager-process throughout and nothing was "left behind".

So it would appear as there isn't any bug.

 

[John Winterbottom]

John;
Using WinXPPro SP2/Office'03
Tried your logic in Access'03 and it worked as it should.
Made the app visible and inserted breakpoints, just to be sure.
Excel opened up as it should and died as soon as the objXL object went out
of scope (set to nothing).
Viewed the task manager-process throughout and nothing was "left behind".

So it would appear as there isn't any bug.

Hi Rob, many thanks - I was hoping someone could test the code for me on
Excel 2003. The issue must be with our local copies of Excel 2003 - we only
have two machines using it at the moment so I can't test on any more.

I will do some more testing today and see if I can narrow down the cause. I
have a feeling this may be coming from an excel Add-In we have installed on
both these machines - I will install 2003 on another machine and see what
happens. I'll post back any results.

 

[RWN]

You're welcome and please do post back.
We're in the process of moving to XP/Off'03 from nt4/off'97 and I'm trying
to find all the surprises now before we roll out the machines.