Instance of Excel still remains after calling Marshal.FinalRelease in VB.NET

Question

I have looked on here for some tips on getting rid of an excel instance after my program runs, but none of the suggestions seem to work. When i run it initially it will create an instance of excel, but while the program is still running and i rerun this code by clicking a button; it will create another instance of excel, but this time it removes the instance that it created leaving only the one that was created when the program was first initially ran.

What i have for code is this so far: (Updated code as of 9/14/2012)

Private Sub GetBatchFileContents()

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim xlRan As Excel.Range
    Dim xlVal(,) As Object
    Dim lastRow As Int32

    xlApp = New Excel.Application()
    xlWB = xlApp.Workbooks.Open(TextBox1.Text.ToString(), _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing, _
                                Type.Missing)
    xlWS = xlWB.Worksheets.Item(1)
    lastRow = xlWS.Cells(xlWS.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
    xlRan = xlWS.Range(xlWS.Cells(1, 1), xlWS.Cells(lastRow, 130))
    xlVal = xlRan.Value2()
    ReleaseObj(xlRan)
    ReleaseObj(xlWS)
    xlWB.Close(False, Type.Missing, Type.Missing)
    ReleaseObj(xlWB)
    xlApp.Quit()
    ReleaseObj(xlApp)

End Sub

Private Sub ReleaseObj(ByRef obj As Object)

    Try
        Marshal.FinalReleaseComObject(obj)
    Catch ex As Exception
        Stop
    Finally
        obj = Nothing
    End Try

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

End Sub

Thanks in advance for the feedback!

Some clarification, `ReleaseObject` is a custom method? As well, if your just reading the file why open the application at all? You can just open the file and read the file in. Is this a 2007+ or 2003- file? — GoldBishop, Sep 14 '12 at 17:10
Yes ReleaseObj is a custom method. I thought that to access the workbook you had to have an excel application to interact with the workbook. the files could be .xls or .xlsx file types. — Jared, Sep 20 '12 at 14:53
Depends, are you just grabbing data out of the Excel document or are you playing with the "child" document and extracting data out of? Cause if you using it basically as a structured flat file (ie csv), then just create a data-connector to the file(s), will access them alot faster and wont have the load-time associate with a new isntance. If you are copy Table/NamedRange copying, then you could do sorta what i did for one of my previous projects for a client. — GoldBishop, Sep 20 '12 at 16:04

Pilgerstorfer Franz · Accepted Answer · 2012-09-20T19:15:22.743

I had a very similar problem to what you described. By using this sample code it worked for me

' set all Excel related  objects to nothing
columnHeaders = Nothing
range = Nothing
endCell = Nothing
startCell = Nothing
excelSheet = Nothing
excelSheets = Nothing
excelWorkbook.Close()
excelWorkbook = Nothing
excelApp.Quit()
' release com ressources 
 Marshal.FinalReleaseComObject(excelApp)  ' !

excelApp = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()

See FinalReleaseComObject and ReleaseComObject at MSDN

EDIT

Private Sub GetBatchFileContents()
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim xlRan As Excel.Range
    Dim xlVal(,) As Object
    Dim lastRow As Int32

    xlApp = New Excel.Application()
    xlWB = xlApp.Workbooks.Open(TextBox1.Text.ToString(), _
                                Type.Missing, Type.Missing,  Type.Missing,  Type.Missing,  Type.Missing, _
                                Type.Missing,  Type.Missing,  Type.Missing, Type.Missing, Type.Missing, _
                                Type.Missing,  Type.Missing, Type.Missing,  Type.Missing)
    xlWS = xlWB.Worksheets.Item(1)

    ' original
    'lastRow = xlWS.Cells(xlWS.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row

    ' new
    Dim range1 As Excel.Range
    range1 = xlWS.Cells(xlWS.Rows.Count, 1)
    Dim range2 As Excel.Range
    range2 = range1.End(Excel.XlDirection.xlUp)
    lastRow = range2.Row
    ReleaseObj(range1)
    ReleaseObj(range2)

    ' original
    'xlRan = xlWS.Range(xlWS.Cells(1, 1), xlWS.Cells(lastRow, 130))

    ' new
    Dim range1_1 As Excel.Range
    range1_1 = xlWS.Cells(1, 1)
    Dim rangeLastRow_130 As Excel.Range
    rangeLastRow_130 = xlWS.Cells(lastRow, 130)
    xlRan = xlWS.Range(range1_1, rangeLastRow_130)
    ReleaseObj(range1_1)
    ReleaseObj(rangeLastRow_130)

    ' unchanged
    xlVal = xlRan.Value2()
    ReleaseObj(xlRan)
    ReleaseObj(xlWS)
    xlWB.Close(False, Type.Missing, Type.Missing)
    ReleaseObj(xlWB)
    xlApp.Quit()
    ReleaseObj(xlApp)
End Sub

I changed your code to assign every possible COM instance to a variable and explicitly release it! But as it works fine on my computer I cannot test it.

Additionally I found an article describing what may cause your described behaviour.

Your Excel loads a managed AddIn(Shared AddIn or VSTO AddIn), which breaks the rule. After your application automates the Excel application, Excel application could not exit correctly because the loaded AddIn has some underlying RCWs not released. In this condition, the Excel does not quit even if your automation client terminates. So a step to troubleshoot this kind of issue would be disabling all AddIns to isolate the root cause.´(see article for full details)

Please let me know if one of my propossals works for you!

**Nevermind it did not work!** It works up until the point i use the Excel.Range object. once that is used then it won't get rid of the initial excel instance created. — Jared, Sep 14 '12 at 14:36
I checked your code with a given xlsx file and Excel Object Libary 14 (Excel2010) - worked as a charm! What library are you using? — Pilgerstorfer Franz, Sep 14 '12 at 15:43
Im using the Microsoft Excel 14.0 Object Library. Another problem maybe that im using a .xls file instead of the new excel version .xlsx. — Jared, Sep 14 '12 at 16:26
I tried both formats and both leave an instance of excel. did you try it while running it in visual studio or test it as a published version? — Jared, Sep 14 '12 at 16:31
I compiled it in debug and in release, published it as clickOnce, started from vs and from programs - worked everytime !? what library do you use? if u want send me your project or relevant pages to franz.pilgerstorfer@gmx.at — Pilgerstorfer Franz, Sep 14 '12 at 18:02
Library? My email will not allow me to send the project cause it has executable files in it. — Jared, Sep 18 '12 at 15:56
Gmail does not like it. So if you use drop box i sent you a link to it on there. — Jared, Sep 20 '12 at 14:36
you could also have just zipped it up and then changed the extension to txt — GoldBishop, Sep 20 '12 at 17:16
got your code - **works fine** - Excel Instance pops up, work got done, Excel Instance ends and is gone. I did some more research and found this [question](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp) Answer is indicating that **any COM instance created** has to be **released explicitly** see updated answer! — Pilgerstorfer Franz, Sep 20 '12 at 19:13
**That works!** I suppose making the excel objects instead of allowing the runtime to do it will allow you to dispose of them properly. Wonder if running it on 64 bit machine causes the problem? When you talk about add-ons is that add-ons for Visual Studio or Microsoft Excel? — Jared, Sep 20 '12 at 20:29
Talking about **Excel AddOns**! So finally as it works now - pls consider voting my answer up and/or selecting it as best answer! After all - glad I could help! — Pilgerstorfer Franz, Sep 20 '12 at 20:35

score 0 · Answer 2 · answered Sep 20 '12 at 16:14

Basic Synopsis of what i did. I incorporated this logic in a Lock Down process in the big-picture of the Workbooks functionality. I had to Turn off Echo, otherwise the end-user got the flashy interaction between the two instances, as i Activated one Workbook/Worksheet and then moved to the other Workbook/Worksheet. There is bound to be something you can use here ;).

This is all i used in my project srcwb.Close SaveChanges:=False and it closed the files. What i have done in the past is to gain a Object reference to Application so i can get ahold of the System ID for the Instance and close it from the Application. This is of course assuming that you have VBA/Project control of the Code-Behind and its not just a flat-data file.

Otherwise you will have to create a Collection Object, in the Parent file, and as you open a new instance store that application instance in the Collection and then periodically run through the Collection and verify that the Instance is alive or not.

Instance of Excel still remains after calling Marshal.FinalRelease in VB.NET

2 Answers2