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

Try reversing the order of released objects so you are removing them in order of decreasing dependency — Alex K., Apr 22 '13 at 09:37
i tried that also but not working. it is the problem with the Range.cells that i am using many times — tushar pathak, Apr 22 '13 at 09:46
Why are you releasing objects in the `If` And why not right at the end? Also it is very difficult to read your code. Can you properly indent it in it's own sub. What I mean is how di `R = CType(range.Cells(4, 2), Excel.Range).Value()` line start after `end sub` Can you post the entire sub, properly indented and as I suggested above. — Siddharth Rout, Apr 22 '13 at 09:55
Also I just noticed that you are releasing object in the Else part of all `IFs` and in some places before else. What happens if the code enters a part of the code where you are not releasing? Are you releasing objects then? (*I can't see that in the code*) — Siddharth Rout, Apr 22 '13 at 10:02
How did this line `R = CType(range.Cells(4, 2), Excel.Range).Value()` come after `End Sub`? — Siddharth Rout, Apr 22 '13 at 10:05
and also i am releasing the objects at the end if all happen correct. — tushar pathak, Apr 22 '13 at 10:05
Can I see your complete sub which has the above code? Is it inside a button click event? If yes then copy the entire sub including the clickevent and indent it and then update the above code. — Siddharth Rout, Apr 22 '13 at 10:07
siddharth i am unable to post the whole code .i have used excel.range for it.there are many iff conditions in my code .it is the problem with range Reference to RCW i tried many things from net but not working. — tushar pathak, Apr 22 '13 at 10:16
many times i am using range.excel .i think the range reference to RCW is not getting released — tushar pathak, Apr 22 '13 at 10:17
Can you show me your Excel declarations at least and how are you releasing the objects in the end? — Siddharth Rout, Apr 22 '13 at 10:17
Public xlApp As Excel.Application Public xlWorkBook As Excel.Workbook Public xlWorkSheet As Excel.Worksheet Public misValue As Object = System.Reflection.Missing.Value Public range As Excel.Range — tushar pathak, Apr 22 '13 at 10:18
and release is releaseObject(range) releaseObject(xlWorkSheet) xlWorkBook.Save() xlWorkBook.Close() releaseObject(xlWorkBook) xlApp.Quit() releaseObject(xlApp) — tushar pathak, Apr 22 '13 at 10:19
Where are you using the range object in your code? Can I see that code? — Siddharth Rout, Apr 22 '13 at 10:21
xlApp = New Excel.ApplicationClass 'xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkBook = xlApp.Workbooks.Open(sFileDir + sFileName) — tushar pathak, Apr 22 '13 at 10:21
xlWorkSheet = xlWorkBook.Worksheets("BOM for Import") range = xlWorkSheet.Cells — tushar pathak, Apr 22 '13 at 10:22
Tushar... If you want me to help you, here is my request for the `LAST` time... Can you please update your post, instead of typing code in the comments? It's really difficult understanding this way. — Siddharth Rout, Apr 22 '13 at 10:23
And when you post the updated code, take care of these things `1` Post the begining of the code where you have the declarations. `2` End of the code i.e before End Sub `3` Few examples of IF-ENDIF statements where you are using/releasing your objects and lastly `4` Indent your code. — Siddharth Rout, Apr 22 '13 at 10:28
@tusharpathak: Send me a message when done. Use `@` like I have done before your name so that I get the message. — Siddharth Rout, Apr 22 '13 at 10:42
@Sid maybe the [one dot rule](http://stackoverflow.com/a/158752/445425) would help here? — chris neilsen, Apr 22 '13 at 10:43
@chrisneilsen: http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/ The problem is not that. I believe Tushar is not releasing objects correctly and where he should. :) — Siddharth Rout, Apr 22 '13 at 10:46
@Sid thanks for the link. As you have noted, looks like OP is releasing in wrong order. Just thought it worth mentioning :) — chris neilsen, Apr 22 '13 at 10:50
@chrisneilsen: I suspect since OP is using lot of `Exit Subs` in IF-Endif, it is very much possible that in one of these IF-ENDIF, he is exiting the sub without releasing objects correctly. — Siddharth Rout, Apr 22 '13 at 10:53
i have asked a new question where my full code is there as i am new to stackoverflow i could't find any way to update the same. — tushar pathak, Apr 22 '13 at 10:57
@SiddharthRout it is not going in any of the if where exit sub is written i had debugged it at the end only it is releasing object — tushar pathak, Apr 22 '13 at 11:05
I want to cover one more angle before I get into this code. Close all open Excel. Open Task Manager. Kill all excel instances if any. Now run your code (by stepping through it. Check these for me `1` Is it exiting the sub using any *Exit Sub* `3` Is an instance still left? — Siddharth Rout, Apr 22 '13 at 11:15
i did it one instance is there in task manager and it is not going in any of if condition where exit sub is there after runnig the code — tushar pathak, Apr 22 '13 at 11:19
Hmmm, I was posting an answer when you typed this comment. Check it out and then we will take it form there... — Siddharth Rout, Apr 22 '13 at 11:35
@SiddharthRout i have changed the code according to u but didn't work now what i did i had just kept 2 if condition in the block here also excel instance remaining in the task manager but if i comment my second condition the excel is getting released. — tushar pathak, Apr 23 '13 at 05:57

score 2 · Answer 1 · answered Apr 22 '13 at 11:34

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

If yes, for example Form Load, then ensure that you are releasing them correctly
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.

Is nice to see a developer enforcing consistent "flow" in methods (Point A). I have same beleive and adhear to it rigidly. Following the idea of being able to see the execution path clearly can only help you in the long run, especially from a maintenance perspective. I also try to avoid statements that make my "flow" jump around like "Continue". I also tend to code an "ELSE" into most of my IF statements, and "CASE ELSE" into "SELECT CASE" regardless of it actually doing anything (just to show the fact it intentionally does not do anything, for when I come back to it a few years later) — DarrenMB, Apr 22 '13 at 14:51
@tusharpathak: If you have decided to not heed to my suggestions then I guess I am just wasting my time :) BEST OF LUCK! — Siddharth Rout, Apr 23 '13 at 06:23
i have tried that also but its my need to put that much of if and else block i cant put single entry and exit point.i have shared my updated code also if u can check it then it is really helpful... — tushar pathak, Apr 23 '13 at 06:33
@SiddharthRout thanx for your help i have solved it the problem was not in my code but was in the releaseObject sub in finally i m not using gc.collect and it worked fine for me — tushar pathak, Apr 23 '13 at 11:35

score 0 · Answer 2 · answered Apr 23 '13 at 11:37

0

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

answered Apr 23 '13 at 11:37

tushar pathak

31
1
5

Excel Application not quitting

2 Answers2