Floating EXCEL.EXE caused by referenced cells in vb.net

Question

I've previously resolved floating EXCEL.EXE files by releasing the worksheet,workbook,and application objects from EXCEL. However, I've noticed that after referencing cell ranges that this .EXE file comes back.

I've commented out the section where i reference these cells and that is in fact what is going on. I've tried releasing the cell itself and the cell's range but that is not working.

I know this may be a repeated question but it's something I'm really having trouble with understanding.

        Dim app As New Microsoft.Office.Interop.Excel.Application
    Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(file)
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
    app.Visible = True
    While (worksheet.Cells.Range("A" & max_row).Value IsNot Nothing)
        max_row = max_row + 1
    End While
    If max_row > 2 Or max_row = 2 Then
        For i As Integer = 2 To max_row Step 1
            MessageBox.Show("here")
            Dim add As DataRow = Me.CentralDatabaseDataSet.Selected_Equipment.NewRow
            If "Section " & worksheet.Cells.Range("J" & i).Text = cboSection.SelectedItem.ToString Then
                add("Type") = worksheet.Cells.Range("A" & i).Text
                ReleaseObject(worksheet.Cells(i, 1))
                'add("Description") = worksheet.Cells.Range("B" & i).Text
                'add("Model Number") = worksheet.Cells.Range("C" & i).Text
                'add("Serial Number") = worksheet.Cells.Range("D" & i).Text
                'add("Asset Number") = worksheet.Cells.Range("E" & i).Text
                'add("Manufacturer ID") = worksheet.Cells.Range("F" & i).Text
                'add("Department ID") = worksheet.Cells.Range("G" & i).Text
                'add("Last Calibration") = worksheet.Cells.Range("H" & i).Text
                'add("Calibration Due Date") = worksheet.Cells.Range("I" & i).Text
                'Me.CentralDatabaseDataSet.Selected_Equipment.AddSelected_EquipmentRow(add)
            End If
        Next
    End If
    book.Close()
    app.Quit()
    ReleaseObject(worksheet)
    ReleaseObject(book)
    ReleaseObject(app)

There's a lot of things to try [here](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects). Personally I would use the [Open XML SDK](https://msdn.microsoft.com/en-us/library/office/bb448854.aspx) with one of the wrappers available (e.g. [ClosedXML](http://closedxml.codeplex.com/), [EPPlus](http://epplus.codeplex.com/), [SpreadsheetLight](http://spreadsheetlight.com/)) and not worry about all those COM interop issues. — Mark, Mar 31 '16 at 21:30
Thank you for the response. I'm working on applying the solutions giving in your link but there's nothing specific to my case. If I figure it out i'll post my new solution. — Rueben.Ramirez, Apr 01 '16 at 14:59
I would think that many of the answers apply specifically to your case - "Never use 2 dots with com objects" is a common theme, and there are many examples in your code where you use multiple dots when calling the COM objects - e.g. in `worksheet.Cells.Range("J" & i).Text` you are not freeing the objects returned by `Cells` and `Range`. You could try adding the `GC.Collect()` and `GC.WaitForPendingFinalizers()` calls and see if that helps (maybe even call them twice, as some suggest). — Mark, Apr 01 '16 at 16:11

score 0 · Accepted Answer · answered Apr 01 '16 at 17:16

You never have to call Marshal.ReleaseComObject() - the better approach is to call the .NET Garbage Collector to clean up by calling GC.Collect().

You have to be careful to ensure that the code talking to Excel is not in the same method as your GC.Collect(), else the debugger might keep objects alive longer than you'd expect.

The general pattern would be:

Sub WrapperThatCleansUp()

    ' NOTE: Don't call Excel objects in here... 
    '       Debugger would keep alive until end, preventing GC cleanup

    ' Call a separate function that talks to Excel
    DoTheWork()

    ' Now Let the GC clean up (twice, to clean up cycles too)
    GC.Collect()    
    GC.WaitForPendingFinalizers()
    GC.Collect()    
    GC.WaitForPendingFinalizers()

End Sub

Sub DoTheWork()
    Dim app As New Microsoft.Office.Interop.Excel.Application
    Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
    app.Visible = True
    For i As Integer = 1 To 10
        worksheet.Cells.Range("A" & i).Value = "Hello"
    Next
    book.Save()
    book.Close()
    app.Quit()

    ' NOTE: No calls the Marshal.ReleaseComObject() are ever needed
End Sub

Floating EXCEL.EXE caused by referenced cells in vb.net

1 Answers1