Excel Application not quitting

Question

Check my code I am automating upload from excel in vb.net here is my code

import statement

Imports Excel = Microsoft.Office.Interop.Excel

releaseObject function

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

button click event

Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ButtonUpload.Click
    If IsPostBack Then
       Dim xlApp As Excel.Application
        Dim xlWorkBooks As Excel.Workbooks
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim range As Excel.Range
        connStr = ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
        conn = New SqlConnection(connStr)
        dat = System.DateTime.Now

        Filepath = Path.GetFullPath(fileUploadBOM.PostedFile.FileName)
        sFileName = Path.GetFileName(fileUploadBOM.PostedFile.FileName)
        FileFormat = Path.GetExtension(Filepath)

        v_bom_type = "IMPORT"
        If FileFormat.Equals(".xls") Or FileFormat.Equals(".xlsx") Then
            System.IO.File.Delete("C:\inetpub\wwwroot\Uploads\" & sFileName)
            fileUploadBOM.PostedFile.SaveAs(sFileDir + sFileName)
            Try
                xlApp = New Excel.ApplicationClass
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(sFileDir + sFileName)
                xlWorkSheet = xlWorkBook.Worksheets("BOM for Import")
                range = xlWorkSheet.Cells
            Catch ex As Exception
                releaseObject(xlApp)
                'GetWindowThreadProcessId(xlApp.Hwnd, processID)
                'release(processID)

            End Try

some if conditions like..

             Dim R As String
            R = CType(range.Cells(4, 2), Excel.Range).Value()
            If Not R Is Nothing Then
                If (R.Trim = "") Then
                    Me.Page.ClientScript.RegisterStartupScript(Me.GetType(), "SetStatusText", "<script type='text/javascript'> alert('please enter the OEM for logistics cost'); </script>")
                    releaseObject(range)
                    releaseObject(xlWorkSheet)
                    xlWorkBook.Save()
                    xlWorkBook.Close()
                    releaseObject(xlWorkBook)
                    xlWorkBooks.Close()
                    releaseObject(xlWorkBooks)

                    xlApp.Quit()

                    releaseObject(xlApp)



                    'GetWindowThreadProcessId(xlApp.Hwnd, processID)
                    'MsgBox(processID)
                    'release(processID)

                    Exit Sub
                Else
                    v_logiccost = CType(range.Cells(4, 2), Excel.Range).Value()
                End If
            Else
                Me.Page.ClientScript.RegisterStartupScript(Me.GetType(), "SetStatusText", "<script type='text/javascript'> alert('please enter the OEM for logistics cost'); </script>")
                releaseObject(range)
                releaseObject(xlWorkSheet)
                xlWorkBook.Save()
                xlWorkBook.Close()
                releaseObject(xlWorkBook)
                xlWorkBooks.Close()
                releaseObject(xlWorkBooks)

                xlApp.Quit()

                releaseObject(xlApp)


                'GetWindowThreadProcessId(xlApp.Hwnd, processID)
                'MsgBox(processID)
                'release(processID)

                Exit Sub
            End If


            '' No of yrs of support

            Dim P As String
            P = CType(range.Cells(6, 2), Excel.Range).Value()
            releaseObject(range)
            If Not P Is Nothing Then
                If (IsNumeric(P) = True) Then
                    v_year = P
                Else
                    Me.Page.ClientScript.RegisterStartupScript(Me.GetType(), "SetStatusText", "<script type='text/javascript'> alert('No Of Years Support Should Be A Number.'); </script>")
                    releaseObject(range)
                    releaseObject(xlWorkSheet)
                    xlWorkBook.Save()
                    xlWorkBook.Close()
                    releaseObject(xlWorkBook)
                    xlWorkBooks.Close()
                    releaseObject(xlWorkBooks)

                    xlApp.Quit()

                    releaseObject(xlApp)



                    'GetWindowThreadProcessId(xlApp.Hwnd, processID)
                    'MsgBox(processID)
                    'release(processID)


                    Exit Sub
                End If
            End If

function calling to releasing at the end

releaseObject(range)
                    releaseObject(xlWorkSheet)
                    xlWorkBook.Save()
                    xlWorkBook.Close()
                    releaseObject(xlWorkBook)
                    xlWorkBooks.Close()
                    releaseObject(xlWorkBooks)

                    xlApp.Quit()

                    releaseObject(xlApp)

this is my whole code i guess the problem is with Range.cells reference to RCW suggest me where i am doing wrong

Answer 1

Thanx all it got solved

Private Sub releaseObject(ByVal obj As Object)
Try

    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)



Catch ex As Exception

Finally
obj = Nothing


End Try
End Sub

Answer 2

Few things

A) I have always been a firm believer of 1 Entry Point and 1 Exit point when it comes to coding. In your code you have several Exit Points. Your code now looks like this

    If A = B Then
        '
        '~~> Rest of the code
        '
        'release object
        Exit Sub
    Else

    End If

    If C = D Then

    Else
        '
        '~~> Rest of the code
        '
        'release object
        Exit Sub
    End If

    releaseObject(xlrange)
    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlApp)

It becomes very difficult to trace if we are closing our objects correctly. The best way is to combine them and then release the objects just before leaving the sub. Your above code can be re-written as

    If A = B Then
        '
        '~~> Rest of the code
        '
    Else
        If C = D Then

        Else
            '
            '~~> Rest of the code
            '
        End If
    End If

    releaseObject(xlrange)
    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlApp)

B) As Alex and Chris mentioned, release your object in the correct order (which you are doing in the end but not in the middle of the code). Like this

    releaseObject(xlrange)
    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlApp)

If you incorporate my first suggestion then you will not have to maintain the release object code everywhere but just in the end.

C) I see that you have declared your Excel Objects as PUBLIC Are you using them somewhere else besides ButtonUpload_Click

1. If yes, for example Form Load, then ensure that you are releasing them correctly
2. If no, then move them inside the ButtonUpload_Click

If you incorporate the above suggestions then I don't see a problem releasing objects when using TWO DOT rule.

Rest I don't see any problem with your existing code. It works just fine for me.