(Another) .net Excel zombie issue

Question

I have a problem with a program leaving an excel zombie process in the background. I've followed all the advice and examples here and MSDN which seek to avoid this but it's driving me nuts. Can anyone spot what is causing the zombie process to occur?

Imports System
Imports System.Collections
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Private Sub LoadExcelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadExcelButton.Click

    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook = Nothing 'instantiated to nothing to help try and avoid zombies
    Dim xlSheet As Excel.Worksheet = Nothing
    Dim STFRange As Excel.Range = Nothing

    Dim Name As String, Easting As Integer, Northing As Integer, tDSpa As Double, Type As String

    Dim NumberSTF As Integer, NumberProperties As Integer, i As Integer, ExcelPath As String

    ExcelPath = Me.ExcelPathTextBox.Text.ToString

    If File.Exists(ExcelPath) = False Then
        MessageBox.Show("Excel file does not exist, exiting.")
        Exit Sub
    End If

    Try

        xlApp.Visible = False

        xlBook = xlApp.Workbooks.Open(ExcelPath) ', , [ReadOnly]:=True

        xlSheet = xlBook.Sheets("STF")

        NumberSTF = xlSheet.UsedRange.Rows.Count - 1 '-1 to account for header
        NumberProperties = xlSheet.UsedRange.Columns.Count

        'create a new collection
        'http://msdn.microsoft.com/en-us/library/xth2y6ft(v=vs.71).aspx
        Dim mySTFCollection As New STFCollection

        For i = 1 To NumberSTF 'rather than a for each loop which would require more excel ranges

            STFRange = xlSheet.Cells(i + 1, 1) '+1 on row to account for header
            Name = STFRange.Value.ToString

            STFRange = xlSheet.Cells(i + 1, 2)
            Easting = CInt(STFRange.Value)

            STFRange = xlSheet.Cells(i + 1, 3)
            Northing = CInt(STFRange.Value)

            STFRange = xlSheet.Cells(i + 1, 4)
            tDSpa = CDbl(STFRange.Value)

            STFRange = xlSheet.Cells(i + 1, 5)
            Type = STFRange.Value.ToString

            Dim objSTF As New STF(Name, Easting, Northing, tDSpa, Type)

            mySTFCollection.Add(objSTF)

        Next i

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

        ReleaseObject(STFRange)
        STFRange = Nothing
        ReleaseObject(xlSheet)
        xlSheet = Nothing

        xlBook.Close(True, , )
        ReleaseObject(xlBook)
        xlBook = Nothing

        xlApp.Quit()
        ReleaseObject(xlApp)
        xlApp = Nothing

    Catch ex As Exception

        MessageBox.Show(ex.Message)

    End Try
End Sub

Public Sub ReleaseObject(ByVal obj As Object)

    Try
        Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

Answer 1

Instead of calling Close() on your Excel app, have you tried Dispose()?

You may want to try doing your cleanup in the Finally block of your code.

Answer 2

1st things 1st

If File.Exists(ExcelPath) = False Then
    MessageBox.Show ("Excel file does not exist, exiting.")
    Exit Sub
End If

Should be outside the Try - End Try and before you instantiate your Excel Objects. If the path doesn't exists you are instructing the code to exit the sub without cleaning up.

2ndly, use GCcollect in the end.

This is my favorite way of handling it

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
        '
        '~~> Rest of code
        '

        '~~> Close workbook and quit Excel
        xlWb.Close (False)
        xlApp.Quit()

        '~~> Clean Up
        releaseObject (xlApp)
        releaseObject (xlWb)
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

If you are keen on Excel Automation from VB.net then I would recommend you going through this link.

FOLLOWUP

Use this code.

Private Sub LoadExcelButton_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim Name As String = "", Type As String = "", ExcelPath As String = ""
    Dim Easting As Integer = 0, Northing As Integer = 0
    Dim NumberSTF As Integer = 0, NumberProperties As Integer = 0, i As Integer = 0
    Dim tDSpa As Double = 0

    ExcelPath = Me.ExcelPathTextBox.Text.ToString

    If File.Exists(ExcelPath) = False Then
        MessageBox.Show("Excel file does not exist, exiting.")
        Exit Sub
    End If

    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook = Nothing
    Dim xlSheet As Excel.Worksheet = Nothing
    Dim STFRange As Excel.Range = Nothing

    xlApp.Visible = True

    xlBook = xlApp.Workbooks.Open(ExcelPath)

    xlSheet = xlBook.Sheets("STF")

    NumberSTF = xlSheet.UsedRange.Rows.Count - 1
    NumberProperties = xlSheet.UsedRange.Columns.Count

    'create a new collection
    'http://msdn.microsoft.com/en-us/library/xth2y6ft(v=vs.71).aspx
    Dim mySTFCollection As New STFCollection

    For i = 1 To NumberSTF

        STFRange = xlSheet.Cells(i + 1, 1)
        Name = STFRange.Value.ToString

        STFRange = xlSheet.Cells(i + 1, 2)
        Easting = CInt(STFRange.Value)

        STFRange = xlSheet.Cells(i + 1, 3)
        Northing = CInt(STFRange.Value)

        STFRange = xlSheet.Cells(i + 1, 4)
        tDSpa = CDbl(STFRange.Value)

        STFRange = xlSheet.Cells(i + 1, 5)
        Type = STFRange.Value.ToString

        Dim objSTF As New STF(Name, Easting, Northing, tDSpa, Type)

        mySTFCollection.Add(objSTF)

    Next i

    '~~> Close the File
    xlBook.Close(False)

    '~~> Quit the Excel Application
    xlApp.Quit()

    '~~> Clean Up
    releaseObject(xlSheet)
    releaseObject(xlBook)
    releaseObject(xlApp)
End Sub

'~~> Release the objects
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