We help IT Professionals succeed at work.

How to cleanly kill an Excel object from memory.

We've all seen it.  That silly EXCEL.EXE stays resident in memory no matter what approach you take.  I've even seen multiple EXCEL.EXE objects appear in the process list in task manager when in fact my code only opens one occurence.  I came up with a way to kill all EXCEL's from memory.  I hope this helps your anguish in the matter.

 
Imports System
Imports System.Diagnostics

Private Sub printdocument1_EndPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles printdocument1.EndPrint

        Do While True
            'kill all resident excels in memory with extreme prejudice
            If CloseAllResidentExcels() = True Then
                Exit Do
            End If
        Loop
        
        'reinit your vars here
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        wbkToShow = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
        wshToShow = DirectCast(wbkToShow.Worksheets("Sheet1"), Excel.Worksheet)

    End Sub
    Private Function CloseAllResidentExcels() As Boolean

        CloseAllResidentExcels = False

        Try
            'loop this process until there are no valid id's to return
            Dim Prc As Process
            Dim localByName As Process() = Process.GetProcessesByName("EXCEL")
            Prc = Process.GetProcessById(localByName(0).Id)
            Prc.Kill()

        Catch ex As Exception
            CloseAllResidentExcels = True
        End Try

    End Function

Open in new window

Comment
Watch Question

What happens when the user intentionally has an instance of Excel open? Forcibly shutting that down would annoy the bejesus out of them.

For the most part, ensuring Excel is gone is fairly straight-forward. Simply dispose of all referenced Excel objects before quitting the Excel Application.

Wayne
BTW, was this intended to be an article ( http://www.experts-exchange.com/articles/ )?
Top Expert 2010

Commented:

The answer for this is in this EE question => http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26227129.html

Same code(in c#) copied below will close EXCEL.EXE immediately...

...
// after you have finished with xlApp

xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

GC.Collect();
GC.WaitForPendingFinalizers();
...





Author

Commented:
Yeah, i have a similar function that checks if any existing excel's are open and warns the user to close currently open excel's  before running the export.  Wayne, closing all excel objects still leaves excel.exe in resident memory.  I've tried all the examples mentioned to no avail.  Maybe it works better in C# but in vb .net it remains alive and well.  Kris, thanks for c# solution.  I'll take a peek at it and see if it can be applied in vb .net

Author

Commented:
oh Kris, I was so bummed when your suggestions didn't work in my vb .net application.  I was thinking "oh yeah, the Garbage collector!"

Author

Commented:
Kris, is there a better way to init my workbook and worksheet to a more interop compliant method?

 
xlApp = New Microsoft.Office.Interop.Excel.Application()
wbkToShow = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
wshToShow = DirectCast(wbkToShow.Worksheets("Sheet1"), Excel.Worksheet)

Open in new window

Author

Commented:
I tried this but got COM error objects when it hit the workbook init line
xlApp = New Microsoft.Office.Interop.Excel.Application
wbkToShow = New Microsoft.Office.Interop.Excel.Workbook
wshToShow = New Microsoft.Office.Interop.Excel.Worksheet

Open in new window

>>Wayne, closing all excel objects still leaves excel.exe in resident memory

Yes, it will, unless you dispose (ie, set to Null or Nothing) of all objects prior to quitting the Excel Application, as I mentioned.

Author

Commented:
that doesn't work consistently.  running the export method several times back to back will still leave resident excel objects in memory.  You will find them in the processes list in task manager.
ReleaseComObject and FinalReleaseComObject don't always work.  (If they ever do).  This is the only way I have been able to quite consistantly kill the ExcelApp in which I'm working.  This does NOT kill any other already-open Excel Files....only the one opened by your app at that time because you pass your ExcelApp object.
 
I use the "Application.DoEvents()" section just to give it a small amount of time to close.  You may not need it.
 

Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Int32, ByVal wMsg As Int32, _
    ByVal wParam As Int32, ByVal lParam As Int32) As Int32

Const WM_QUIT = &H12


Private Sub ExcelWorker

    MyExcel As Microsoft.Office.Interop.Excel.ApplicationClass
    
    'Blah Blah Blah Do all your Excel Stuffz

    MyExcel.Quit

    PostMessage(Excel.Hwnd, WM_QUIT, 0, 0)

    Dim i As Int32
    For i = 0 To 10000
        Application.DoEvents()
    Next

    MyExcel = Nothing

End Sub

Open in new window

Author

Commented:
PuffDaddy! you da man!  That worked perfectly!!!!!!!

Author

Commented:
I ran every possible test to break this code, and it cleanly removed the target excel object BEAUTIFULLY!  Thanks for this excellent catch.  Everyone who deals with excel objects MUST try this solution.