none
Cannot release Excel.Range Object RRS feed

  • Question

  • I'm using VB .NET and the Excel COM interop. I know about all the ways to properly close Excel in .NET after using the Office-Interop class and usually it works, but now that I'm using a Range Object in my method it is simply impossible to get rid of. If I comment out the Range part Excel closes properly. If I don't, the process will not close. I kept simplifying the code until I came up with the example below, which still doesn't work.

    I simply cannot find a way to use a Range Object and have Excel close properly. 'm pretty sure it worked under Windows 7. I'm nearly 100% sure but not quite because it has been a while. When I got back to the code and wanted to finish up the project we had installed Windows 10 at work. When I wanted to retest my method I found that Excel wasn't closing anymore so I dumbed down the code until I found out the Range Object was the culprit

     Dim oExcel As Object
            oExcel = CreateObject("Excel.Application")
    
            Dim oBooks As Excel.Workbooks = Nothing
            Dim oBook As Excel.Workbook = Nothing
            Dim oSheet As Excel.Worksheet = Nothing
    
            oBooks = oExcel.Workbooks
            oBook = oBooks.Add
            oSheet = oExcel.worksheets(1)
    
            oSheet.Name = "Title"
    
            Dim rng As Excel.Range
            rng = oSheet.Cells(1, "A")
            rng.Value = "test"
            release(rng)
    
            oBook.SaveAs(fileName, Type.Missing)
            oBook.Close()
            oExcel.Application.Quit()
            oExcel.Quit()
    
            release(rng)
            release(oSheet)
            release(oBook)
            release(oBooks)
            release(oExcel)
    
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
    
        End If
    
    End Sub
    
    Private Sub release(ByRef Obj As Object)
        If Obj IsNot Nothing Then Marshal.FinalReleaseComObject(Obj)
        Obj = Nothing
    End Sub

    Wednesday, April 27, 2016 12:15 PM

Answers

  • There is a lot of false information and confusion about this issue.

    First - you never have to call Marshal.ReleaseComObject(...) or Marshal.FinalReleaseComObject(...) when doing Excel interop. It is a confusing anti-pattern, but any information about this, including from Microsoft, that indicates you have to manually release COM references from .NET is incorrect. The fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references. For your code, this means you can remove the whole "release(...)" Sub and calls to it.

    Second, if you want to ensure that the COM references to an out-of-process COM object is cleaned up when your process ends (so that the Excel process will close), you need to ensure that the Garbage Collector runs. You do this correctly with calls to GC.Collect() and GC.WaitForPendingFinalizers(). Calling twice is safe, end ensures that cycles are definitely cleaned up too.

    Third, and this is the issue I think you ran into - when running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So your GC.Collect() calls are not effective for cleaning object like rng.Cells from the same method. You should split the code doing the COM interop from the GC cleanup into separate methods.

    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

    -Govert

    Excel-DNA - Free and easy .NET for Excel


    • Edited by Govert van Drimmelen Friday, April 29, 2016 9:12 AM Fix typo
    • Marked as answer by Mongan Monday, June 20, 2016 10:56 AM
    Friday, April 29, 2016 9:08 AM
  • Tried your code on Windows 7 and Visual Studio Express 2013 and no Excel process residue exists when the program terminates. Cell A1 = test in the resulting Excel file. Even commented out the two lines release(rng) and still no Excel process remaining.

    Can you try your code on another computer?


    Best regards, George



    • Edited by George.B.Summers Wednesday, April 27, 2016 2:37 PM
    • Marked as answer by Mongan Thursday, April 28, 2016 7:22 AM
    Wednesday, April 27, 2016 2:34 PM
  • Hi Mongan,

    I have test your code using vs2015 and Excel2016.

    after running your code I find that it release the objects correctly.

    there is no process of Excel in the Task Manager.

    you have mentioned that you are pretty sure that it will work on windows7.

    do you think it will not work with any other os?

    now we currently have 2 results of testing.

    as I have mentioned above I tested above code on windows10, vs2015 and Excel2016 and it release the objects.

    George.B.Summers has also tested your code in windows 7 and in vs2013.

    he also get same result like me that there is no process of Excel in Task Manager.

    so here I think you should test it on different machines and check what output you get by them.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mongan Thursday, April 28, 2016 7:22 AM
    Thursday, April 28, 2016 2:49 AM
    Moderator

All replies

  • Error messages?

    Best regards, George

    Wednesday, April 27, 2016 12:33 PM
  • None. If I comment out the code below there is no Excel process left over in task manager, if I leave it in there is always a process after using the function.

          Dim rng As Excel.Range
            rng = oSheet.Cells(1, "A")
            rng.Value = "test"
            release(rng)

    Wednesday, April 27, 2016 1:31 PM
  • Ok, here are some clues from people who made it work:

    http://stackoverflow.com/questions/18154315/excel-process-not-closing


    Best regards, George

    Wednesday, April 27, 2016 1:38 PM
  • Thanks, but I am well aware of these solutions. All of them work... until I use Excel.Range
    Wednesday, April 27, 2016 1:59 PM
  • What about this?

    Having run into this headache very recently too, I'd comment that if you use an unqualified Range or Worksheet or Workbook even once it can keep the process open.  Every single command on the workbook should reference fully -     excelapp.excelbook.excelsheet.range("A1")  I went crazy the other day with this until I realized my .Find function included an unqualified "Range" in its arguments


    Best regards, George

    Wednesday, April 27, 2016 2:05 PM
  • ..and this ...

    Avoid using double-dot-calling expressions, such as this:

    var workbook = excel.Workbooks.Open(/*params*/)

    ...because in this way you create RCW objects not only for workbook, but for Workbooks, and you should release it too (which is not possible if a reference to the object is not maintained).


    Best regards, George

    Wednesday, April 27, 2016 2:08 PM
  • Tried your code on Windows 7 and Visual Studio Express 2013 and no Excel process residue exists when the program terminates. Cell A1 = test in the resulting Excel file. Even commented out the two lines release(rng) and still no Excel process remaining.

    Can you try your code on another computer?


    Best regards, George



    • Edited by George.B.Summers Wednesday, April 27, 2016 2:37 PM
    • Marked as answer by Mongan Thursday, April 28, 2016 7:22 AM
    Wednesday, April 27, 2016 2:34 PM
  • Hi Mongan,

    I have test your code using vs2015 and Excel2016.

    after running your code I find that it release the objects correctly.

    there is no process of Excel in the Task Manager.

    you have mentioned that you are pretty sure that it will work on windows7.

    do you think it will not work with any other os?

    now we currently have 2 results of testing.

    as I have mentioned above I tested above code on windows10, vs2015 and Excel2016 and it release the objects.

    George.B.Summers has also tested your code in windows 7 and in vs2013.

    he also get same result like me that there is no process of Excel in Task Manager.

    so here I think you should test it on different machines and check what output you get by them.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mongan Thursday, April 28, 2016 7:22 AM
    Thursday, April 28, 2016 2:49 AM
    Moderator
  • Thank you for your help George and Deepak. It appears I hadn't tried the most cliché thing in all of IT: Turning it off and on again. No idea how restarting windows is related to any of this but the day I wrote the post nothing I did could keep Excel from leaving a process alive. After hearing it worked on your and Deepak's PC I decided to try again today and without changing a single line of code, not even one character, it worked.

    I'm gonna continue working on my project now and see if the problem pops up again before marking any answer, but as of now it seems the problem just vanished.

    Edit: It works. Tried it on the main project and it is still working. This makes zero sense. I tested it endless times over and never got it working with a Range object. This morning, not a single line of code changed and it works.
    • Edited by Mongan Thursday, April 28, 2016 7:25 AM Did more testing
    Thursday, April 28, 2016 6:49 AM
  • There is a lot of false information and confusion about this issue.

    First - you never have to call Marshal.ReleaseComObject(...) or Marshal.FinalReleaseComObject(...) when doing Excel interop. It is a confusing anti-pattern, but any information about this, including from Microsoft, that indicates you have to manually release COM references from .NET is incorrect. The fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references. For your code, this means you can remove the whole "release(...)" Sub and calls to it.

    Second, if you want to ensure that the COM references to an out-of-process COM object is cleaned up when your process ends (so that the Excel process will close), you need to ensure that the Garbage Collector runs. You do this correctly with calls to GC.Collect() and GC.WaitForPendingFinalizers(). Calling twice is safe, end ensures that cycles are definitely cleaned up too.

    Third, and this is the issue I think you ran into - when running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So your GC.Collect() calls are not effective for cleaning object like rng.Cells from the same method. You should split the code doing the COM interop from the GC cleanup into separate methods.

    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

    -Govert

    Excel-DNA - Free and easy .NET for Excel


    • Edited by Govert van Drimmelen Friday, April 29, 2016 9:12 AM Fix typo
    • Marked as answer by Mongan Monday, June 20, 2016 10:56 AM
    Friday, April 29, 2016 9:08 AM
  • That is very interesting. I have mostly read the exact opposite on the internet, primarily on StackOverflow. They all suggest calling Marschall.FinalReleaseComObject() and many have claimed that if you follow the no triple dots rule then you won't need to and shouldn't call the garbage collector. If you are right then there is some serious misinformation circling through the internet.

    I do believe I read an MSDN page that even mentioned calling MarshalReleaseComObject. I couldn't find the link and don't have it here, but I can check over the weekend when I'm back home.

    Friday, April 29, 2016 1:36 PM
  • You're right that there is lots of terrible advice on this both at StackOverflow and on MSDN posts from Microsoft staff.

    What finally convinced me to have a closer look and figure out the right advice was this post https://blogs.msdn.microsoft.com/visualstudio/2010/03/01/marshal-releasecomobject-considered-dangerous/ together with finding the issue with references kept alive under the debugger on some StackOverflow answer.

    Now I have a set of rules for the Excel COM interop that make sense and work reliably. Indeed - Marshal.ReleaseComObject() and Marshal.FinalReleaseComObject() are dangeours and should never be called when doing Excel COM interop.

    -Govert

    Excel-DNA - Free and easy .NET for Excel.


    Saturday, April 30, 2016 10:36 AM
  • This is a little late, but I happend to stumble upon the Microsoft article that proposed the initial solution I tried. Apparently it is outdated and delivered as is, but I still find it very misleading that it exists with that little reliable information on the subject out there. I'm using your solution now and am happy with it.

    Here is the article

    Monday, June 20, 2016 10:58 AM