Reading excel files in vb.net leaves excel process hanging

Question

The following code works fine but seems to leave instances of excel.exe running in the background. How do I go about closing out this sub properly?

    Private Sub ReadExcel(ByVal childform As Fone_Builder_Delux.frmData, ByVal FileName As String)
    ' In progress
    childform.sampleloaded = False
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Open(FileName)
    xlWorkSheet = xlWorkBook.Worksheets(1)
    Dim columnrange = xlWorkSheet.Columns
    Dim therange = xlWorkSheet.UsedRange


    childform.datagridHeaders.Columns.Add("", "") ' Super imporant to add a blank column, could improve this
    For cCnt = 1 To therange.Columns.Count

        Dim Obj = CType(therange.Cells(1, cCnt), Excel.Range)
        childform.datagridSample.Columns.Add(Obj.Value, Obj.Value)
        childform.datagridHeaders.Columns.Add(Obj.Value, Obj.Value)

    Next

    For rCnt = 2 To therange.Rows.Count
        Dim rowArray(therange.Columns.Count) As String
        For cCnt = 1 To therange.Columns.Count

            Dim Obj = CType(therange.Cells(rCnt, cCnt), Excel.Range)
            Dim celltext As String
            celltext = Obj.Value.ToString
            rowArray((cCnt - 1)) = celltext
            'MsgBox(Obj.Value)

        Next
        childform.datagridSample.Rows.Add(rowArray)
    Next

    AdjustHeaders(childform)
    childform.sampleloaded = True
End Sub

Answer 1

I ran into this problem and what I found worked was making sure I called the Close() method on all Workbook and Workbooks objects, as well as the Quit() method on the Excel Application object. I also call System.Runtime.InteropServices.Marshal.ReleaseComObject on every Excel object was instantiated. I do all this in reverse order of age, so the newest object gets cleaned up first and the oldest, which is the Application object, gets taken care of last. I don't know if the order really matters, but it seems like it might.

I've seen examples where GC.Collect() was called at the very end, but I've never had to do that to get the excel.exe process to end.

Answer 2

Short answer: close each item appropriately, then call FinalReleaseComObject on them.

GC.Collect()
GC.WaitForPendingFinalizers()

If xlWorkSheet Is Nothing Then Marshal.FinalReleaseComObject(xlWorkSheet)
If xlWorkBook Is Nothing Then
    xlWorkBook.Close(false, false)
    Marshal.FinalReleaseComObject(xlWorkBook)
End If
xlApp.Quit()
Marshal.FinalReleaseComObject(xlApp)

Long answer: read this answer to another question (the entire post is helpful too).