Q317109

[Yeager Simpson]

Several messages to microsoft newsgroups have been posted regarding excel's
persistence until a program is shut down, but there seems to be no answer.

KB article Q317109
http://support.microsoft.com/default.aspx?scid=kb;en-us;317109 seems to not
work? This question seems to be unanswered for VB net.

I don't have vb net. Programming with Visual Basic 6, the same behaviour is
easily reproduced - excel in the running processes list that won't go away.

try this:

'---------------------------------------
Dim oXLApp As Excel.Application
Dim oXLWb As Excel.Workbook
Dim oXLWs As Excel.Worksheet

Set oXLApp = New Excel.Application
oXLApp.DisplayAlerts = False

Set oXLWb = oXLApp.Workbooks.Add
oXLWb.Worksheets.Worksheets(1).Name = "my sheet"

Set oXLWs = oXLWb.Worksheets("my sheet")

'-- insert some data to excel

oXLApp.Quit
'// the above statement optional whether to close the instance of excel,
leave it open, save it prior in VB, whatever.

Set oXLWs = Nothing
Set oXLWb = Nothing
Set oXLApp = Nothing

'------------------------------
The excel object will stay in the process list no matter what combination of
the above is entered until the calling program is shut down. KB Q317109 says
this is by design. ahhhhhhhhhhh.

Help please. I want to get rid of excel without my users shutting down the
program.

 

[Jim Cone]

Yeager,

Following is my "canned" answer to the won't quit issue.
In addition, the line of code...
oXLWb.Worksheets.Worksheets(1).Name = "my sheet"
should read...
oXLWb.Worksheets(1).Name = "my sheet"

'-------------------------------------------------------------
Here are some general guidelines to use when automating Excel...

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%3ben-us%3b178510
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%3ben-us%3b189618
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%3ben-us%3b319832
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....
'-------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Naveen]

Jim

Your note has been extremely helpful.

Before I read this I had been searching for a while to resolve the
persistence of EXCEL.EXE process.

Thanks very much.

Naveen

 

[DGT3]

I have been working on making excel go away, only in VC++, using the example
code I found in KB articles 186120, 186122, and 179706.

So far I have narrowed it down to using the Open method of the Workbooks
object.
If I start Excel, add a workbook, put some information in it, save the
workbook and call the Quit method on the application object, Excel disappears
from the system's task list, just like its supposed to.

If I start Excel, get a reference to the workbooks object, though
GetWorkbooks, and open a workbook, (with no other action taking place) Excel
disappears from view, but won't exit the systems task list.

Next approach is grabbing a snapshot of the processes in the system, before
and after Excel is starting, then killing off the new copy of Excel when I
need Excel to exit. Not pretty, but have no other ideas at present...

There is a lot of extra potential for my application if I can solve this,
any help is appreciated.

David

 

[scorpion53061]

Hi,

This works for me. I hope it helps you.

Private Function closeExcel() As Short
Dim count As Short = 0
Dim excelInstance As System.Diagnostics.Process
Dim excelInstances() As Process =
System.Diagnostics.Process.GetProcessesByName("Excel")
For Each excelInstance In excelInstances
Try
excelInstance.Close() '<-- you can use close or kill .. up to you
excelInstance.Kill()
count += 1
Catch ex As Exception
End Try
Next
Return count
End Function

http://www.kjmsolutions.com/datasetarray.htm