Pan Pizza Pan Pizza - 5 months ago 34
Vb.net Question

The proper way to dispose Excel com object using VB.NET?

I have following code (obtained from online tutorial). The code is working but I suspect the way to dispose the Excel com object is somewhat not proper. Do we need really need to call GC.Collect? Or what is the best way to dispose this Excel com object?

Public Sub t1()
Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook = oExcel.Workbooks.Open(TextBox2.Text)

'select WorkSheet based on name
Dim oWS As Excel.Worksheet = CType(oBook.Sheets("Sheet1"), Excel.Worksheet)
Try

oExcel.Visible = False
'now showing the cell value
MessageBox.Show(oWS.Range(TextBox6.Text).Text)

oBook.Close()
oExcel.Quit()

releaseObject(oExcel)
releaseObject(oBook)
releaseObject(oWS)
Catch ex As Exception
MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!")
End Try
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

Answer

@PanPizza C# and VB.NET are very similar, remove the ; from the end of the line, Worksheets sheets = ... becomes Dim sheets Worksheets = .... If you're interested in getting better at programming you should really learn how to transition between both as many .NET examples are only provided in one or the other and you are really limiting yourself.

As mentioned in this answer: How to properly clean up Excel interop objects in C# "Never use two dots" this means always step down into a single sub-object and never do this Dim oWS AS Excel.Worksheet = oExcel.Worksheets.Open(...) always step down to workbook and then step down to the worksheet, never directly from the Excel.Application.

As a general rule what you need to do is release your items in the reverse order to that which they were created. Otherwise you're taking the feet out from underneath your other references and they won't correctly deallocate.

Notice how you create Excel Application (oExcel), then Excel Workbook (oBook) and then finally Excel Worksheet (oWS), you need to release them in the reverse order.

Thus your code becomes:

    oBook.Close()
    oExcel.Quit()

    releaseObject(oWS)
    releaseObject(oBook)
    releaseObject(oExcel)
Catch ex As Exception

and just remove this code entirely from the Sub releaseObject(ByVal obj As Object)

Finally
    GC.Collect()

It's not needed, GC occurs naturally and don't expect your applications to instantly free up memory, .NET pools unallocated memory so that it can readily instance objects in this memory rather than having to ask the OS for more memory.