none
Why Excel doesn't close?

    Question

  • I use the code below to start Excel, create a new workbook and do other stuff.
    There are 2 things that I don't understand:

    1) Run the code and close Excel while the Console.Read is waiting. Why does the Excel windows disappear, but the application is still running?

    2) Remove the WB = Excel.WorkBooks.Add() and run the code. Why does Excel disappear? I was expecting that the Excel.Interactive = True would keep it alive until the user closes it manually.

      Sub Main()  
        Dim Excel As Object  
        Excel = CreateObject("Excel.Application")  
        Excel.Visible = True 
        Excel.Interactive = True 
     
        Dim WB As Object  
        WB = Excel.WorkBooks.Add()  
        WB = Nothing 
     
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)  
        Excel = Nothing 
        GC.Collect()  
     
        Console.Read()  
      End Sub  

    Thanks,
    Stefano
    Sunday, June 22, 2008 5:19 PM

Answers

  • Hi Stefano.  You need to understand the difference between the COM object (what shows up in Task Manager as a process) and the object in your application that points to that unmanaged COM object (you xlApp variable).  When you create an instance of Excel as a variable in your app your application is essentially creating references to that unmanaged object.  If you do not explicitly release those references then the unmanaged object will persist.  This is a common problem when working with COM and why it is critical to remove those references in a disciplined way when building them into your projects.  I like to use a helper method to accomplish the task as in the example below...

     
        Private xlApp As Excel.Application  
        Private WB As Excel.Workbook  
     
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
            xlApp = New Excel.Application  
            WB = xlApp.Workbooks.Add()  
            xlApp.Visible = True 
            ReleaseComObject(WB)  
            ReleaseComObject(xlApp)  
            GC.Collect()  
            GC.WaitForPendingFinalizers()  
        End Sub  
     
        Friend Sub ReleaseComObject(ByVal o As Object)  
            Try  
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o)  
            Catch  
            Finally  
                If o IsNot Nothing Then o = Nothing 
            End Try  
        End Sub 

    Please note that after I release the resources I also call the garbage collector.  Despite what JayStation said -- which is true for managed objects that implement the IDisposable interface -- this has a very benficial effect with unmanaged objects that allows the Process to be cleaned up immediately (when all references are removed) rather than waiting for your application to close. 

    So open Task Manager and then run the code above.  Click the button and after the workbook appears close it.  Notice that the EXCEL process disappears immediately.  NOW, comment out the two GC calls and do the same thing...  the process will remain in Task Manager until you close your application.

    Now, if you open the application, making it visible for the user to close either before or after the app is done then you are guaranteed that the process will not persist -- it will be killed eventually (when the user chooses).  This is true even if you do not call ReleaseCOMObject.   However, if you open the Excel app and keep it hidden -- say you are adding rows to a worksheet -- then you need to call ReleaseCOMObject to kill the process.  This is where people usually get it wrong.  In this case it is also critical that you properly close the objects (Close for the workbook, Quit for the app) because the objects will persist even if you release them.  Think of it as you having the responsibility to metaphorically click the Close button in the title bar if you never expose the app to the user.  So JayStation has it right again there...  it just eneds to have the ReleaseComObject calls added...

     Private xlApp As Excel.Application  
        Private WB As Excel.Workbook  
     
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
            xlApp = New Excel.Application  
            WB = xlApp.Workbooks.Add()  
            'xlApp.Visible = True 'Commented out.
            WB.Close(False)  
            xlApp.Quit()  
            ReleaseComObject(WB)  
            ReleaseComObject(xlApp)  
        End Sub  
     
        Friend Sub ReleaseComObject(ByVal o As Object)  
            Try  
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o)  
            Catch  
            Finally  
                If o IsNot Nothing Then o = Nothing 
            End Try  
        End Sub 

    Notice that I didn't include the GC calls.  I only include them if I care about having the process killed immediately rather than when the app closes. This distinction may become important if your app has the user performing this action many times in the lifetime of the application -- but then in that case you might consider never releasing the app object, just reusing it until your app closes where in the FormClosed event handler you then call Quit (if it is Not Nothing) and RelseaseComObject.

    So there is no need to use Process.Start and GetObject.  Just manage your COM references correctly.
    Monday, June 23, 2008 7:44 PM

All replies

  • WB = Nothing and Excel = Nothing aren't doing anything to the workbook or the application instance -- only to your variables.

    Try this:

    WB.Close(False'False to not save changes  
     
    Excel.Quit()  
     

    Remember to call Dispose on objects that support it, and note that it is typically bad practice to call GC.Collect() -- this will often reduce performance to your user.
    Monday, June 23, 2008 10:49 AM
  • Thanks JayStation3, but I don't want to close neither the workbook nor Excel.
    I want to start Excel, open a workbook, prepare some stuff for the user and leave it there for the user to work on it.
    Then I want to unlink Excel from my application, so that the user will be free either to close Excel before closing my application or to keep it open after my application is closed.

    Instead (1) Excel remains open until I close the application even after the user closes Excel.

    And (2) if I open Excel and do not create the workbook then Excel closes even if I don't tell Excel to close.

    Stefano
    Monday, June 23, 2008 1:10 PM
  •  Oh, I see now.

    Instead of CreateObject, use:
    Process.Start("excel.exe"

    And then to set your variable:
    Dim Excel As Microsoft.Office.Interop.Excel.Application = GetObject(, "Excel.Application"

    I always add some functionality to detect the number of running Excel processes, because it is possible that there may be more than one, which is potentially a problem because GetObject can't discriminate among them.
    Monday, June 23, 2008 1:21 PM
  • Hi Stefano.  You need to understand the difference between the COM object (what shows up in Task Manager as a process) and the object in your application that points to that unmanaged COM object (you xlApp variable).  When you create an instance of Excel as a variable in your app your application is essentially creating references to that unmanaged object.  If you do not explicitly release those references then the unmanaged object will persist.  This is a common problem when working with COM and why it is critical to remove those references in a disciplined way when building them into your projects.  I like to use a helper method to accomplish the task as in the example below...

     
        Private xlApp As Excel.Application  
        Private WB As Excel.Workbook  
     
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
            xlApp = New Excel.Application  
            WB = xlApp.Workbooks.Add()  
            xlApp.Visible = True 
            ReleaseComObject(WB)  
            ReleaseComObject(xlApp)  
            GC.Collect()  
            GC.WaitForPendingFinalizers()  
        End Sub  
     
        Friend Sub ReleaseComObject(ByVal o As Object)  
            Try  
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o)  
            Catch  
            Finally  
                If o IsNot Nothing Then o = Nothing 
            End Try  
        End Sub 

    Please note that after I release the resources I also call the garbage collector.  Despite what JayStation said -- which is true for managed objects that implement the IDisposable interface -- this has a very benficial effect with unmanaged objects that allows the Process to be cleaned up immediately (when all references are removed) rather than waiting for your application to close. 

    So open Task Manager and then run the code above.  Click the button and after the workbook appears close it.  Notice that the EXCEL process disappears immediately.  NOW, comment out the two GC calls and do the same thing...  the process will remain in Task Manager until you close your application.

    Now, if you open the application, making it visible for the user to close either before or after the app is done then you are guaranteed that the process will not persist -- it will be killed eventually (when the user chooses).  This is true even if you do not call ReleaseCOMObject.   However, if you open the Excel app and keep it hidden -- say you are adding rows to a worksheet -- then you need to call ReleaseCOMObject to kill the process.  This is where people usually get it wrong.  In this case it is also critical that you properly close the objects (Close for the workbook, Quit for the app) because the objects will persist even if you release them.  Think of it as you having the responsibility to metaphorically click the Close button in the title bar if you never expose the app to the user.  So JayStation has it right again there...  it just eneds to have the ReleaseComObject calls added...

     Private xlApp As Excel.Application  
        Private WB As Excel.Workbook  
     
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click  
            xlApp = New Excel.Application  
            WB = xlApp.Workbooks.Add()  
            'xlApp.Visible = True 'Commented out.
            WB.Close(False)  
            xlApp.Quit()  
            ReleaseComObject(WB)  
            ReleaseComObject(xlApp)  
        End Sub  
     
        Friend Sub ReleaseComObject(ByVal o As Object)  
            Try  
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o)  
            Catch  
            Finally  
                If o IsNot Nothing Then o = Nothing 
            End Try  
        End Sub 

    Notice that I didn't include the GC calls.  I only include them if I care about having the process killed immediately rather than when the app closes. This distinction may become important if your app has the user performing this action many times in the lifetime of the application -- but then in that case you might consider never releasing the app object, just reusing it until your app closes where in the FormClosed event handler you then call Quit (if it is Not Nothing) and RelseaseComObject.

    So there is no need to use Process.Start and GetObject.  Just manage your COM references correctly.
    Monday, June 23, 2008 7:44 PM
  • Thanks Dig-Boy, your answer is good for the first half of my question:
    1) Why does Excel remain alive after the user closes it? Because I forgot the ReleaseComObject(WB). I added it and now it works.

    But it doesn't explain the second half:
    2) Why does the visible and interactive Excel close when I release it?
    This code should leave Excel alive:

      Sub Main()  
        Dim Excel As Object 
        Excel = CreateObject("Excel.Application")  
        Excel.Visible = True 
        Excel.Interactive = True 
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)  
        Excel = Nothing 
        GC.Collect()  
        GC.WaitForPendingFinalizers()  
        Console.Read()  
      End Sub 

    Using JayStation's advice it works, but I still would like to know why it doesn't work in your way:

      Sub Main()  
        Dim Excel As Object  
        Try  
          Excel = GetObject(, "Excel.Application")  
        Catch ex As Exception  
          Process.Start("excel.exe")  
          Do  
            Try  
              Threading.Thread.Sleep(50)  
              Excel = GetObject(, "Excel.Application")  
            Catch ex2 As Exception  
            End Try  
          Loop While Excel Is Nothing  
        End Try  
        Dim WB As Object  
        WB = Excel.WorkBooks.Add()  
        System.Runtime.InteropServices.Marshal.ReleaseComObject(WB)  
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)  
        WB = Nothing 
        Excel = Nothing 
        GC.Collect()  
        GC.WaitForPendingFinalizers()  
        Console.Read()  
      End Sub 

    Another question: I'm declaring Excel As Object instead of As Excel.Application because I think that if my application has no reference to any Excel object model and uses a generic object with late binding, then I will have less problems using the application in different computers with different versions of Excel.
    Do you agree?

    Stefano

     

    Wednesday, June 25, 2008 1:12 AM
  • That is a good question -- but I think it has nothing to do with the Interactive property.  In fact, as far as I understand that property is set to True by default and remains True until you explicitly set it to False.  It is really there to keep users from messing with the UI during intensive operations (like complex macro operations).  It should have no bearing on whether the application object is in some kind of "active" state beyond normal.

    I believe the answer to question 2 comes from the fact that you have not opened (or added) any workbooks to the application object.  Without a "hook" to an existing workbook or temporary one the application apparently has no "reason" to stay open when you realease the COM object.  I'm not sure why this is -- I agree with you that it shoudl default to the benefit of the doubt that you created the object for a reason.  Yet another way that Excel intuits your intentions and gets it wrong.

    So try adding the following line to your code example...

            Dim Excel As Object 
            Excel = CreateObject("Excel.Application")  
            Excel.Visible = True 
            Excel.Workbooks.Add()  
            'Excel.Interactive = True  
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Excel)  
            Excel = Nothing 
            GC.Collect()  
            GC.WaitForPendingFinalizers() 

    ...and the application will stay open.

    Wednesday, June 25, 2008 2:21 AM