Excel Process not closing in VB.net

Question

I am creating an excel file using interop.excel and the process is not closing. This is the code i am trying to use.

 Private Sub converToExcel(fileLoc As String, ds As DataSet)
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkBooks As Excel.Workbooks
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    xlApp = New Excel.Application
    xlWorkBooks = xlApp.Workbooks
    xlWorkBook = xlWorkBooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To ds.Tables(0).Columns.Count - 1
            xlWorkSheet.Columns.NumberFormat = "@"
            xlWorkSheet.Cells(i + 1, j + 1) = String.Format("{0}", ds.Tables(0).Rows(i).Item(j).ToString())
        Next
    Next

    xlWorkSheet.SaveAs(fileLoc)
    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkBooks)
    releaseObject(xlApp)

End Sub
Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

I think i am missing a COM object but cant seem to find a solution. Also as a note, this is running on 64-bit Windows 8. Any help would be great! Thanks

Answer 1

Manual memory management like this just never works. This is a problem that's been known for very a long time and the core reason that garbage collectors were invented. Programmers just forever forget to release memory.

It gets extra hard when you can't see the memory being used. Which is certainly the case in your code, the xlWorkSheet.Cells(i + 1, j + 1) expression uses no less than three references. One for the range object returned by the Cells property, one for a sub-range object selected by i+1 and another for the sub-range object selected by j+1. Very nice syntax sugar provided by the VB.NET language, writing COM code without it is pretty doggone painful. But not helpful to let you see the references. Not only can't you see it in your source code, there is absolutely nothing the debugger can do to help you see them either.

This is very much a solved problem in .NET, it has a garbage collector and it can see everything. The most basic problem is that you don't give it a chance to solve your problem. The mistake you made is that you stopped. Probably by setting a breakpoint on the last statement and then looking in Task Manager and seeing Excel.exe still running. Yes, that's normal. Garbage collection is not instant.

Calling GC.Collect() is supposed to make it instant, but that doesn't work in the specific case of running the Debug build of your project. The lifetime of local variables gets then extended to the end of the method, help you see them in the Autos/Locals/Watch window. In other words, GC.Collect() doesn't actually collect any of the interface references. More about that behavior in this post.

The simple workaround is to not stop. Keep doing useful things to give the garbage collector a reason to run. Or letting your program terminate since it is done, Excel terminates when the finalizer thread runs for the last time. Which works because the local variables that had the references are not in scope anymore.

But everybody wants the instant fix anyway. You get it by deleting all the releaseObject() calls. And doing it like this instead:

converToExcel(path, dset)
GC.Collect()
GC.WaitForPendingFinalizers()

Or in other words, force a collection after the method has returned. The local variables are no longer in scope so they can't hold on to an Excel reference. It will now also work when you debug it, like it already did when you ran the Release build without a debugger.

Answer 2

Try System.Runtime.InteropServices.Marshal.FinalReleaseComObject, that should help... also you should call xlWorkBook.Close() and xlapp.quit, if I recall correctly. First call them and then set them to nothing.