Removing Excel.exe from memory

[Rob Oldfield]

This seems to be a topic that has been discussed at length in newsgroups,
but I still can't get it to behave...

I'm using automation to get at an existing Excel spreadsheet and then
retrieve data from it. I then want to close down the Excel process. What
I'm getting though, is the process remaining in memory until my app shuts
down.

I am, by the way, aware that MS article 257819 is a better method of getting
at the data, but sadly the spreadsheets that I'm working with are created by
another piece of software and they're just not quite right. The ADO
approach doesn't work with them.

My code (referencing MS article 317109 and numerous newsgroup postings) -
just a sample to simply open Excel and the file, and then to shut it
down....the form will need a reference to the Excel object library.....

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

'Open the file
Dim file As String = "c:\data\vs\test.xls"
Cursor.Current = Cursors.WaitCursor
Dim objExcel As Excel.Application
Dim Books As Excel.Workbooks
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
objExcel = New Excel.Application()
Books = objExcel.Workbooks
Book = Books.Open(file)
Sheet = Book.Worksheets(1)
Sheet.Unprotect()

'...and then attempt to close it
Dim objEachWorkSheet As Excel.Worksheet
Dim objEachWorkBook As Excel.Workbook
For Each objEachWorkBook In objExcel.Workbooks
For Each objEachWorkSheet In objEachWorkBook.Worksheets
NAR(CObj(objEachWorkSheet))
Next
objEachWorkBook.Close(False)
NAR(CObj(objEachWorkBook))
Next
NAR(objExcel.Workbooks)
objExcel.Quit()
NAR(CObj(objExcel))
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
Cursor.Current = Cursors.Default
End Sub

Private Sub NAR(ByVal o As Object)
Try
Dim i As Integer
Do
i = System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Loop While i > 0
Catch excpUnexpected As Exception
MsgBox(excpUnexpected.ToString())
Finally
o = Nothing
End Try
End Sub

Anyone have a method of putting the Excel process out of it's misery?

 

[Matthew '.NET 247' Reynolds]

Hi Rob,

A fairly brutal and inelegent way of killing Excel, or indeed any process is
to use System.Diagnostics.Process to discover the process that you want to
kill and kill the Kill method.

This is a fairly (very?) dodgy solution that I wouldn't advocate in any but
the most hacky situations (such as you're just trying to get some debug code
working or, this is a one off utility that will never find its way into
proper production use, etc.), but I offer it as a suggestion just in case
you are trying to hack something together!!!

 

[Brian P. Hammer]

I hvae not done it in .Net but had a similar problem in Access. Below are
some code sinipts I used to get around the problem. They might help you
out.

Function IsExcelRunning() As Boolean
Dim xlApp As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear
End Function

Private Sub Excel_Imports()
ExcelRunning = IsExcelRunning()
boolOpen = True
If ExcelRunning Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If

Set MySheet = xlApp.workbooks.Open(strConnect)
Set sheet1 = MySheet.sheets("BASE MODEL")

'Do all my work here

MySheet.Application.activeworkbook.Save
MySheet.Application.activeworkbook.Close

boolOpen = False

If Not ExcelRunning Then xlApp.Quit
Set MySheet = Nothing
Set sheet1 = Nothing

End Sub

 

[Rob Oldfield]

Hmmmm. That looks like a plan. I was playing with it earlier but my
machine has now decided to go horribly wrong (we're talking major
reinstallation type stuff) so I can't really see if this is going to
work... but...

What I was thinking (in order to protect existing Excel processes that
users may have) was to fill an array with existing process IDs for
Excel before I open the spreadsheet, open it up and play with it as
before, and then fill a second array with Excel process IDs as they
stand after my code has run. I can then compare those two arrays to
return the process ID for my new one, and then kill that.

Could you please explain why you think that this is a dodgy method? I
realise it's a bit of a kludge, but I can't see any reason why it's
not workable. (The length of time my processing will take is never
going to be enough to allow a user to start it, and then go off to a
freshly opened Excel spreadsheet before the processing is finished.)

 

[Rob Oldfield]

I think you're right Brian that it's something to do with making sure
that everything is set back to "nothing", but that's what the NAR
function is designed to do. If it's leaving anything open then I
can't see it.