Excel COM object not released (vb.net)

Question

I'm struggling to find a solution for this funky behavior for the COM logic within Visual Studio. I have already reduced the 2DOT notation into smaller parts... nothing seems to work. There is always a remnant of excel in the memory.

Even In this link i had no success. Any guidance or help here would be mostly appreciated. I have no idea on what else to do or try...

Here is my code:

Private Sub FrmListTables_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim XL As Object : XL = New Excel.Application
    Dim WBS As Workbooks = XL.Workbooks
    Dim WB As Workbook = WBS.Open(WorkbookPath)

    LstSheets.Items.Clear
    For Each Sheet As Worksheet In WB.Worksheets
        LstSheets.Items.Add(Sheet.Name)
    Next

    WB.Close(False)
    WBS.Close()
    XL.Quit()

    ReleaseComObject(WB)
    ReleaseComObject(WBS)
    ReleaseComObject(XL)

    WB = Nothing
    WBS = Nothing
    XL = Nothing
End Sub

Private Sub ReleaseComObject(ByRef obj As Object)
    Try
        Do Until Runtime.InteropServices.Marshal.ReleaseComObject(obj) <= 0
        Loop
    Catch
    Finally
        obj = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

While `Marshal.ReleaseComObject()` / `Marshal.FinalReleaseComObject()` can be used in some limited scenarios, it's quite complicated and mostly useless to handle COM refs like this. Read this: [Clean up Excel Interop Objects with IDisposable](https://stackoverflow.com/a/25135685/7444103) and try this instead: [As of today, what is the right way to work with COM objects?](https://stackoverflow.com/a/38170605/7444103) — Jimi, Aug 11 '21 at 10:54
Thank you so much. Actually by separating into a helper routine and then using the garbage collector it manages to clean up the memory! — Rui Manso, Aug 11 '21 at 13:37

score 0 · Answer 1 · answered Aug 11 '21 at 13:41

With the help of Jimi the code is now properly disposing unnecessary COM objects. The code is now as follows:

 Private Sub LoadExcelSheets()
    Dim XL As New Excel.Application
    Dim WBS As Workbooks = XL.Workbooks
    Dim WB As Workbook = WBS.Open(WorkbookPath)
    Dim Sheet As Worksheet
    LstSheets.Items.Clear()

    For Each Sheet In WB.Worksheets
        LstSheets.Items.Add(Sheet.Name)
    Next

    WB.Close(False)
    WBS.Close()
    XL.Quit()

    Sheet = Nothing
    WB = Nothing
    WBS = Nothing
    XL = Nothing
End Sub

Private Sub FrmListTables_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    LoadExcelSheets()

    GC.Collect()
    GC.WaitForPendingFinalizers()
End Sub

That is absolutely not correct! For example, what are you going to do if an exception is thrown in the code? Your GC methods will not be called at all! — Eugene Astafiev, Aug 11 '21 at 14:39
Instead of simply pointing out that's wrong, could you provide some suggestion for a more correct approach? — Rui Manso, Aug 11 '21 at 23:40

Excel COM object not released (vb.net)

1 Answers1