none
Excel ReleaseComObject doesn't work

    Question

  • I'm not sure what the deal is, but I'm having a problem with this. I've tried all variations of the releascomobject I've found and none of them seem to work. I'm opening an existing file and updating some columns, then print, save, close.

                $Excel = New-Object -ComObject Excel.Application
                Write-Verbose "Open the $($FileName) spreadsheet"
                $Excel.Workbooks.Open($FileName)
                Write-Verbose "Open the $($WorkSheetName) worksheet"
                $WorkSheet = $Excel.Worksheets.Item($WorkSheetName)
                Write-Verbose "Select column $($VolumeIDColumn), the Volume Identification column"
                $Range = $WorkSheet.Range($VolumeIDColumn)

    Do the updates

                $Excel.DisplayAlerts = $false
                Write-Verbose "Saving $($FileName)"
                $Excel.Save()
                Write-Verbose "Closing $($WorkSheetName)"
                $Excel.Workbooks.Close()
                Write-Verbose "Exit Excel"
                $Excel.Application.Quit()

    Do the close

            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet)
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

    I've also tried FinalReleaseComObject to no avail. Is there a way to determine the Process ID of the instance of my $Excel object and then just call stop-process with that ID? I've grep'd through the object and not found anything that looks like a process id or that matches the process id that is currently running.

    For the record, I'm running Windows 7 Enterprise X64 and Office 2010 x86.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Thursday, May 17, 2012 9:23 PM

Answers

  • jrv,

    there were no notifications being displayed. the code above was a blank sheet i created with nothing in it. and for the record on my desktop at work, i notice the same behavior, x64 powershell the code i pasted above works fine and excel closes, on the x86 powershell, excel does not close with the same code.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

     

    Jeffrey, Richard, All -

    Let me clarify this a bit more.

    The code I posted which uses the release method of InteropServices is required.  Without it you cannot cleanly release Excel or any Office automation server.  This is not necessarily the case with all automation servers.

    Some servers will be removed when the variable is released by GC.  This does not work in Excel./Office so we are forced to do it manually.  The Office and Powershell teams have commented on this and some sense that it might be fixed seems to exist.  For now we do it manually.

    I also wanted to stress that, while forcing GC will not cause a problem, it will not solve this one either.  It will appear to work if you open Excel then immediately quite and run GC after removing the variable:

    $xl=New-Object –com Excel.Application
    $xl.Quit()
    Remove-Variable xl
    [System.GC]::Collect()

    This will appear to remove Excel.  Interesting thing is that much of the time if you wait just a bit (sometimes for a few minutes) Excel will terminate by itself.  On my slowere XP it seems to never terminate until we do a GC.

    This appear to be because the variable that represents the main proxy to Excel does execute a cleanup that correctly dereferences the Excel object and so it will close.  This can take quite a while on a busy system.

    The following is nearly instantaneous:

    $xl=New-Object –com Excel.Application
    $xl.Quit()
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}

    The GC method will nearly always fail if other variables contain references.  Dereferences all assigned objects until the reference count is zero.  That is what the while loop is doing.  Optionally remove the variable and, after releasing and removing all variables run GC although I claim that GC on other than the main proxy will not dereference the variable.

    I am not claiming that this will fix all issues with terminating Excel.  If you are running with a debugger then you may have to logoff or reboot to get Office to behave correctly.  PowerShell can corrupt the memory of Office processes when stepping through code or executing a line at a time.  Office 2003 was terrible it nearly always would get hung.  Office 2007 is much better and , so far, Office 2010 seems to not misbehave when automated from PowerShell although I have not tested extensively.


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 5:15 PM

All replies

  • Hi,

    Try

    [System.GC]::Collect()

    after releasing the COM objects.

    Bill

    Thursday, May 17, 2012 9:31 PM
    Moderator
  • No love. I just get 3 as the output.

    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Thursday, May 17, 2012 9:33 PM
  • Releasing com objects should be immediate.

    Ther can be multiple holds on an object.  Each time it is accessed without a release the object will 'count-up' another hit.

    This will get all refrences released.

    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet)){'released one count'}

    It will loop until the count is zero.  Do this for every object you created.

    In your example you have missed releasing the $range object so it may be what is holding you open.

    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Range)){'released one count'}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheet)){'released one count'}while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){'released one count'}


    ¯\_(ツ)_/¯


    • Edited by jrv Thursday, May 17, 2012 9:38 PM
    Thursday, May 17, 2012 9:38 PM
  • Try using this function to release your $WorkSheet and $Excel objects:

    function release-comobject($ref) {
      while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref) -gt 0) { }
      [System.GC]::Collect()
    }
    

    e.g.;

    release-comobject $WorkSheet
    release-comobject $Excel

    Bill

    Thursday, May 17, 2012 9:40 PM
    Moderator
  • Hi,

    jrv's point is also valid. You must release every object reference you open and he noticed it doesn't look like you're releasing $Range.

    Bill


    Thursday, May 17, 2012 9:49 PM
    Moderator
  • I might also point out that, contrry to popular beliefe, garbage colelction has nothing to do with it.  Also GC will not collect an object that is assigned to a variable inPOwerShell.  If you need GC to clean up then you neeed to also Release-Variable <varname> before calling GC.

    Even this will not release a COM object that still has references.  The GC is very lazy.  It will release memory held by objects but cares less about reference objects.  Automation Server objects are just that, they are references. 

    Even if you GC the refrence the server has not been told that it is not being used.  Some servers get dereferencesd on destruction of the proxy. Excel seems to have issues for some reason.  Always just dereference.  I have never seen Excel not shutdown. You can show Excel in Task Manager and watch it immediately quit after you have removed the last reference.  Leave a reference and you can run GC all day and Excel will never shutdown.  GC does nothing when using Excel.

    The rules of COM are quite different for InProc and automation servers.  Excel is a server which runs in its own memory space.  Excel is responsible for its own termintation.  Once you 'de-refrence' the obejct completey Excel will check to see if anyone else is using the server. If not it will shut down.  If you manually access the server it can pin it in memory.  It is best to run servers hidden if you want to be sure they are removed although Excel behaves better than many do.


    ¯\_(ツ)_/¯

    Thursday, May 17, 2012 10:16 PM
  • @jrv Hi again!

    I tried what you suggested and still nothing here is the script (https://code.google.com/p/mod-posh/source/browse/powershell/production/Update-DPMSpreadSheet.ps1) the first go around I tried to releasecomobject($Range), and then $Worksheet and finally $excel and nothing happened. I still had excel running perhaps you can see something that I"m totally missing.

    When that didn't work I tried AbqBill's suggestion of [System.GC]::Collect() which i tried at the end of everything and in between each of the releaseobjects still no go. Finally I tried the while loops and that didn't work either, so I feel I'm either not conveying everything, or am just plain dumb.

    The function i've tried before and it didn't work either. in fact I entered it directly into the ISE, created an instance of excel, didn't open any spreadsheets, and passed the function the excel object and it didn't close.

    I'll try this when i get home in case there is some difference between my personal laptop and my office desktop. aside from the OS, which laptop is Windows 7 Ultimate x64. otherwise everything else is the same.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Thursday, May 17, 2012 10:21 PM
  • I have had trouble with this issue for some time and never found a solution. The problem only occurs in PowerShell. Similar VBScript code simply uses:

    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit

    and all is well. But in PowerShell I use code similar to below:

    Do {$x = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Columns)} While ($x -gt -1)

    for every Excel object reference used in the code. I try to release in reverse order of creation, so that $Excel is the last object to be released. Sometimes this works, but usually I find Excel objects left in memory (using Task Manager). For the moment, I've decided there is no solution.


    Richard Mueller - MVP Directory Services

    Thursday, May 17, 2012 11:06 PM
    Moderator
  • I have had trouble with this issue for some time and never found a solution. The problem only occurs in PowerShell. Similar VBScript code simply uses:

    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit

    and all is well. But in PowerShell I use code similar to below:

    Do {$x = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Columns)} While ($x -gt -1)

    for every Excel object reference used in the code. I try to release in reverse order of creation, so that $Excel is the last object to be released. Sometimes this works, but usually I find Excel objects left in memory (using Task Manager). For the moment, I've decided there is no solution.


    Richard Mueller - MVP Directory Services


    Richard - same code as :
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Range)){'released one count'}

    WHILE continues until return from release is 'false'.


    ¯\_(ツ)_/¯

    Thursday, May 17, 2012 11:14 PM
  • @jrv Hi again!

    I tried what you suggested and still nothing here is the script (https://code.google.com/p/mod-posh/source/browse/powershell/production/Update-DPMSpreadSheet.ps1) the first go around I tried to releasecomobject($Range), and then $Worksheet and finally $excel and nothing happened. I still had excel running perhaps you can see something that I"m totally missing.

    When that didn't work I tried AbqBill's suggestion of [System.GC]::Collect() which i tried at the end of everything and in between each of the releaseobjects still no go. Finally I tried the while loops and that didn't work either, so I feel I'm either not conveying everything, or am just plain dumb.

    The function i've tried before and it didn't work either. in fact I entered it directly into the ISE, created an instance of excel, didn't open any spreadsheets, and passed the function the excel object and it didn't close.

    I'll try this when i get home in case there is some difference between my personal laptop and my office desktop. aside from the OS, which laptop is Windows 7 Ultimate x64. otherwise everything else is the same.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com


    Try this while watching Task Mangaer.  Be sure Excel is not working:

    $xl=new-object -com Excel.Application
    $xl.Visible =$true
    $xl.quit()
    # at this point excel will not quit.
    sleep 10 # seconds
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){'released one count'}
    # Excel is now gone

    I have been down this path dozens of times.  It works correctly when you do it correctly.  If anything is not correct then you get back bad info and Excel will not shutdown.

    If you open a corrupt document you need to cancle all messages in order to close and quit cleanly.  We will get to that later.


    ¯\_(ツ)_/¯

    Thursday, May 17, 2012 11:18 PM
  • Hi,

    I'll second what jv said - I created an automation project using Excel on a Windows Server 2008 R2 box that referenced and released all of the COM objects and Excel always disappears from the task list when the script is finished. One of my rules of thumb is never to use two "." in a reference - only use one "." and assign to a variable, and then release the variable (using the function I posted above) when finished.

    Bill

    Friday, May 18, 2012 12:06 AM
    Moderator
  • The function is really overkill and tthe GC, as I posted, doesn't really do anything.  It will work withINProc objects but has no effect on Autoamtion Servers like Excel.  If you read up on COM and automation you will see why.

    If an Excel instance is not realeased then it is because you are still holding a refrence count. Every object you assign ot any part of an Excel object must be released.  If another process touches Excel it can stop it from being removed from memory no matter waht you do in PowerShell.  If the Document is corrupted yo will not be able to shut Excel down until you force a fix or force the document closed.  All of this is by design.  If Excel is not visible you will not be able to see prompts asking for the documetn to be saved or fixed or any other prompts that will block the object from terminating.


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 12:34 AM
  • IIRC, the manual calls to the GC Release() method were required for the script to work properly. Without the Release() method Excel was staying in memory.

    Bill

    Friday, May 18, 2012 1:16 AM
    Moderator
  • IIRC, the manual calls to the GC Release() method were required for the script to work properly. Without the Release() method Excel was staying in memory.

    Bill

    Where are you getting that rule from?

    The object is relweased as soon as you remove the reference.  It has nothing to do witrh releasing the proxy from memory. GC release allows a variables memory to be freed. 

    [System.Runtime.Interopservices.Marshal]::ReleaseComObject

    This releases the reference to the SERVER.  It is this reference that the SERVER holds that determies when Excel will shut down.  Excel cares nothing about variables in another program.  Once we tell Excel - or any other automation server - that we are removing the refrence Excel can close. 

    Excel will close when th3 refrences to it are zero for ALL programs.  PowerShell is just one program that may be using the server.  Excel, by default is NOT multi-instanced.  One server manages all open workbooks.  It will always close whenever a 'Quit is pending and the reference count is zero.  That is fundamental COM automation. 

    I think you are mixing up how PowerSHell releases memory and COM refernce releases. 

    An object will not be garbage collected until it its removed (Remove-Variable or it goes out of scope).  It will then only be removed on a GC pass.  The pass may not release all memory on  a single pass.  Calling GC directly may help it to release memory GC 'Collect only tells GC to perfom a 'collect' pass.  There is never a guarantee that it will free any memory.

    Excel will still shutdown once the reference count reaches zero.  The reference count will go to zero without any involvement by GC.  I shutdown Excel all of the time in this way and never remove the variables.  I may reuse them or they may go out of scope.  I have never had to call GC.

    The point oof this is that if Excel is hanging it is because a variable still holds a reference count.  Find and de-refrence the variable and Excel, assuming no other process is using it, will terminate.

    .


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 1:37 AM
  • So that seems to be the common theme, release in reverse order.

    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Friday, May 18, 2012 1:58 AM
  • My testing was a year ago, so I don't remember all details, but I never got a PowerShell script that uses Excel to consistently clean up properly. Scripts that read a spreadsheet, create a new spreadsheet, or update an existing spreadsheet all leave Excel objects in memory. The scripts raise no errors and work as intended, but if I run the script 10 times, I'll have 10 Excel objects in memory. When I test today with Excel 2007 on Windows 7 Professional my scripts always leave the Excel object in memory. I can't remember if it was better in XP. The following example fails to clean up for me:


    # Spreadsheet file.
    $File = "c:\rlm\PowerShell\Users.xlsx"

    $Excel = New-Object -ComObject "Excel.Application"
    $Workbooks = $Excel.Workbooks
    $Workbook = $Workbooks.Open($File)
    $Worksheets = $Workbook.Worksheets
    $Sheet = $Worksheets.Item(1)
    $Sheet.Name = "User Information"

    # Populate spreadsheet.
    $Sheet.Cells.Item(1, 3) = "James L. Smith"
    $Sheet.Cells.Item(2, 3) = "jsmith"
    $Sheet.Cells.Item(3, 3) = "James L. Smith"
    $Sheet.Cells.Item(4, 3) = "cn=James L. Smith,ou=West,dc=MyDomain,dc=com"

    # Save spreadsheet and close the workbook.
    $Excel.Save()
    $Workbook.Close()
    # Quit Excel.
    $Excel.Quit()
    # Release all COM objects.
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet) > $Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheets) > $Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) > $Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbooks) > $Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) > $Null
    Remove-Variable Excel

    -----



    Richard Mueller - MVP Directory Services

    Friday, May 18, 2012 2:05 AM
    Moderator
  • jrv

    I ran the code below, and received two very different results.

    $Excel = New-Object -ComObject Excel.Application
    $Excel.Workbooks.Open('C:\Users\Patton\Documents\My Dropbox\ours\Schedule.xlsx')
    $WorkSheet = $Excel.Worksheets.Item('Sheet1')
    $Range = $WorkSheet.Range('D:D')
    $Excel.Quit()
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($range)){'released one count'}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)){'released one count'}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)){'released one count'}

    When this code was run against an x64 instance of Powershell, excel closed. When this same code was run against an x86 instance of powershell excel did not close.

    Thoughts?


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Friday, May 18, 2012 2:09 AM
  • Richard - I jusust ran you exact code  on XP and WIn7.  It runs corerectly and does nto leave Excel in memory.

    If you open Excel do you see any damaged workbooks asking to be repaired?


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 2:40 AM
  • Jeffery. It i smy experisnce that Excel not closing is caused by either a damaged workbook needing reapir or a damaged Excel installation.  The machine I am on didn't work correctly until about a year ago I ran a complete repair on Office.  It worked well fdor quite a while but every now an then start failing.  In all cases I open Excel and there are one or more workbooks that need to be repaired.

    You should also try turning off all prompts as a Prompt will prevent Excel from closing.

    Goot go.  I iwll be back in 30 mniutes.


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 2:44 AM
  • Here is the code to disable all alerts.  This will prevent invisible message boxes from preventiong Excel from terminating.

    $excel.DisplayAlerts = $False

    It goes just before workbook save or close.


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 3:13 AM
  • No, I always open the workbook and it looks fine.


    Richard Mueller - MVP Directory Services

    Friday, May 18, 2012 3:26 AM
    Moderator
  • jrv,

    there were no notifications being displayed. the code above was a blank sheet i created with nothing in it. and for the record on my desktop at work, i notice the same behavior, x64 powershell the code i pasted above works fine and excel closes, on the x86 powershell, excel does not close with the same code.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Friday, May 18, 2012 1:52 PM
  • jrv,

    there were no notifications being displayed. the code above was a blank sheet i created with nothing in it. and for the record on my desktop at work, i notice the same behavior, x64 powershell the code i pasted above works fine and excel closes, on the x86 powershell, excel does not close with the same code.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

     

    Jeffrey, Richard, All -

    Let me clarify this a bit more.

    The code I posted which uses the release method of InteropServices is required.  Without it you cannot cleanly release Excel or any Office automation server.  This is not necessarily the case with all automation servers.

    Some servers will be removed when the variable is released by GC.  This does not work in Excel./Office so we are forced to do it manually.  The Office and Powershell teams have commented on this and some sense that it might be fixed seems to exist.  For now we do it manually.

    I also wanted to stress that, while forcing GC will not cause a problem, it will not solve this one either.  It will appear to work if you open Excel then immediately quite and run GC after removing the variable:

    $xl=New-Object –com Excel.Application
    $xl.Quit()
    Remove-Variable xl
    [System.GC]::Collect()

    This will appear to remove Excel.  Interesting thing is that much of the time if you wait just a bit (sometimes for a few minutes) Excel will terminate by itself.  On my slowere XP it seems to never terminate until we do a GC.

    This appear to be because the variable that represents the main proxy to Excel does execute a cleanup that correctly dereferences the Excel object and so it will close.  This can take quite a while on a busy system.

    The following is nearly instantaneous:

    $xl=New-Object –com Excel.Application
    $xl.Quit()
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}

    The GC method will nearly always fail if other variables contain references.  Dereferences all assigned objects until the reference count is zero.  That is what the while loop is doing.  Optionally remove the variable and, after releasing and removing all variables run GC although I claim that GC on other than the main proxy will not dereference the variable.

    I am not claiming that this will fix all issues with terminating Excel.  If you are running with a debugger then you may have to logoff or reboot to get Office to behave correctly.  PowerShell can corrupt the memory of Office processes when stepping through code or executing a line at a time.  Office 2003 was terrible it nearly always would get hung.  Office 2007 is much better and , so far, Office 2010 seems to not misbehave when automated from PowerShell although I have not tested extensively.


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 5:15 PM
  • If you are using PowerShell v2, this function should do it.
    Friday, May 18, 2012 6:25 PM
  • I think I got it. The following works and leaves nothing in memory. Notice how I release all Excel objects:


    $File = "c:\Scripts\Example.xlsx"
    $Excel=New-Object –com Excel.Application
    $Workbooks = $Excel.Workbooks
    $Workbook = $Workbooks.Open($File)
    $Worksheets = $Workbook.Worksheets
    $Sheet = $Worksheets.Item(1)
    $Cells = $Sheet.Cells
    $Item = $Cells.Item(3, 1)
    $Value = $Item.Text
    "Value = $Value"

    $Workbook.Close()
    $Excel.Quit()
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Item)){}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Cells)){}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)){}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkSheets)){}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)){}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbooks)){}
    while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){}

    -----

    If I use $Sheet.Cells.Item(3, 1).Text, or $Sheet.Cells the script still works, but leaves objects in memory. I realize now this is what Bill meant in a previous reply (only one "." in reference). You cannot release the Items or Cells objects if you have no object reference to release (or you need some other method to release these).


    Richard Mueller - MVP Directory Services

    Friday, May 18, 2012 7:02 PM
    Moderator
  • Richard.  I have never done it that way and have never had an issue once all of the gotchas have been eliminated.  In fact I prefer to not rerference everything.

    Do you have the Interop installed?  It is optional on Office.  I suspect that that could be part of the problem.  I have it installed everywhere so I cannot easily test.


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, May 18, 2012 7:28 PM
    Friday, May 18, 2012 7:24 PM
  • If you are using PowerShell v2, this function should do it.

    That function makes a lot of assumptions that may not be true unless you follow all of teh rules.  It also woill not successfully release Excel objects htat hav enot been dereferenced. 

    The function has been around.  It is the old GC method. It wolls on InProc objects nad to a small degree on Office automation servers.  It will not work unless you are certian that all variables are removed.  The issue is that you may hav eother com objects that yu do not want removed so it, also , will cause issues.

    Dereferncing seems to work best under all circumstances.

    Look closely at teh function.  It does exactly what Bilss code was trying to do but does remove the variable before doign the GC.  This works until, as Richard has just pointed out, we get many objects referenced for Excel then it fails.  Richard's method is working now for his instance.  It is carefull preventing interim references from occurring.  I suggest that Interop helps to expose the methods on teh objects so that the object can be completely derefreferenced,  I know form experience that even this will fail if you inadvertently overwrite an object.

    $wb=$xl.WorkBooks[1]
    $wb=$xl.Workbooks[2]

    This will usually create un unreachable object that will prevent Excel from terminating no matter what you do.

    Enumerating an excel collection in a loop can also cause this.

    Not all Excel components or collections seem to cause this isseu.  The Cells collection has not done this to me as I enumerate it quite frequently and am still able to shut Excel down.. The Workbooks collection and, I believe, the WorkSheets collection seem to hang Excel when enumerated and altered.

    I am curious if not installing teh Interop assembly may be part of the variation in behaviors we are seeing.


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 7:44 PM
  • I designed the function to be called at the end of a script/function, not in the console —unless the code is wrapped in a child execution context/scope. I have been using it (PS v2) for over two years and never had a problem with it. Could you post a sample code where the function fails to release a COM object? I am curious…

    Here is Rchard's test but with my function:

    # load Remove-ComObject first
    Get-Process excel -ErrorAction SilentlyContinue
    & {
        $File = "c:\Scripts\Example.xlsx"
        $Excel=New-Object –com Excel.Application
        $Workbooks = $Excel.Workbooks
        $Workbook = $Workbooks.Open($File)
        $Worksheets = $Workbook.Worksheets
        $Sheet = $Worksheets.Item(1)
        $Sheet = $Worksheets.Item(2) # <-- inadvertently overwrite an object
        $Cells = $Sheet.Cells
        $Item = $Cells.Item(3, 1)
        $Value = $Item.Text
        "Value = $Value"
        $Workbook.Close()
        $Excel.Quit()
        Remove-ComObject -Verbose
    }
    Start-Sleep -Milliseconds 250
    Get-Process excel -ErrorAction SilentlyContinue
    
    Friday, May 18, 2012 9:13 PM
  • jv, I think you are right about Interop. The documentation here:

    http://msdn.microsoft.com/en-us/library/15s06t57.aspx

    states that the primary interop assembly (PIA) enables managed code to interact with Office application's COM-based object  model. All of my PC's that have Office have what I believe is the relevant dll, Microsoft.Office.Interop.Excel.dll (version 12.0), and they also have the .NET framework (2.0 or above). But that doesn't mean the dll is registered, or Interop is installed (working). In fact, I must Release all Excel objects on both of my Windows 7 computers. But on my XP and Windows Server 2003 computers, with PowerShell V1, I only need to release the objects you release, and nothing is left in memory. Since I installed Office the same way on all machines (as far as my notes indicate from 1.5 years ago), I wonder if the PowerShell version matters.

    In any case, I think not having Interop installed is a good theory. In my case, my intent is to develop code that works for the most people, not just me (and PowerShell guru's), so I don't really want to enable/install Interop. I now know how to modify my code so it will work for the most people.

    Is there a way to check if Interop is installed, other than finding the dll?


    Richard Mueller - MVP Directory Services


    Friday, May 18, 2012 10:28 PM
    Moderator
  • I have tried teh Reelase-Comnbject function. It works about 95% of the time or more but every once in aawhile it doesn't remove teh object.  It happens most often if there is an exception.

    The Marshall::Release method does not seem to fail that way but I haven't tried it hundreds of times.

    To check if Interop is installed cprrectly I think this will work,

    18:32 PS>$Excel=New-Object -com Excel.Application
    18:39 PS>[Microsoft.Office.Interop.Excel.Constants]
    IsPublic IsSerial Name                                     BaseType
    -------- -------- ----                                     --------
    True     True     Constants                                System.Enum


    ¯\_(ツ)_/¯

    Friday, May 18, 2012 10:39 PM
  • I have tried teh Reelase-Comnbject function. It works about 95% of the time or more but every once in aawhile it doesn't remove teh object.  It happens most often if there is an exception.

    ...


    ¯\_(ツ)_/¯


    Maybe you are confusing my function with someone else's, my function is called Remove-ComObject http://gallery.technet.microsoft.com/office/d16d0c29-78a0-4d8d-9014-d66d57f51f63 but thanks. Anyway, if you ever run into a problem with Remove-ComObject in PS v2, I would apreciate any feedback. PowerShell v3 beta broke the function's implementation because of optimized variables; I'll have to wait until PS v3 is out of beta to adjust it.
    • Edited by zx38 Friday, May 18, 2012 11:08 PM
    Friday, May 18, 2012 11:05 PM
  • Same one

    ¯\_(ツ)_/¯

    Friday, May 18, 2012 11:50 PM
  • zx38 - don't get mewrong.  Your function is very good.  It is well packaged too.  It has a couple of drawbacks due to how the GC works.  For almost any normal script that does not use much memory it will likely work however if the system is very busy then GC may just ignore the objects if other older objects are around and memory is not low.  As best I can remember GC does not attemp to release every object on every pass.

    The Marshall::Release call does not rely on GC but decrements the counters directly ( I believe GC does this indirectly when 'Dispose' is called).  You can see this when calling Release in that the final call will cause Excel to disappear almost instantaneously.  No GC or Release-Variable are needed.

    Both techniques have drawbacks.  Your method is very convenient if you don't care about any of the objects and don't care to keep track. 

    It is interesting that VBScript never needs this treatment.  If you 'Quit' Excel successfully Excel terminates.  The issue seems to be in the Net Framework Interop assembly.  It apparently does not clean up memory for objects that have been terminated and PowerShell does not do a GC pass when it is exiting.


    ¯\_(ツ)_/¯



    • Edited by jrv Saturday, May 19, 2012 12:29 AM
    Saturday, May 19, 2012 12:27 AM
  • zx38 - don't get mewrong.  


    ¯\_(ツ)_/¯



    By no means jrv, I do understand your well-defined and valid points, having to manage this ComObject-PowerShell flaw has frustrated many —if not all— PowerShell scripters. In v1 days, the most commonly used option to get rid of Excel's ComObjects was to obliterate them with kill (Stop-Process). The problem is definitely in some Office APIs, I have only seen this die hard behavior in a few, I have never experienced the same with Word, Access or Outlook. The call to GC's Collect method is necessary in my function to coerce PowerShell to release the ComObjects even after the variables are removed, comment it out and run the previous test to verify this.

    I wanted to exclude Marshall's ReleaseComObject and keep the function in pure PowerShell and it took me some time to come up with the technique I use in the function, which is to truly remove these variables (containing ComObjects) from memory by piping the actual System.Management.Automation.PSVariable object to Remove-Variable and, afterwards, press PowerShell to cleanup with GC's Collect. At the end, I could not keep the function in pure PowerShell and had to reach out to .Net garbage management… but I did exclude Marshall's ReleaseComObject method ;)


    Saturday, May 19, 2012 2:29 AM
  • Ok, so this thread is very helpful, sorry I'm getting back to it so late. After reading what JRV said and what Richard posted I found that while I knew I was referencing the following objects

    $Excel = New-Object -ComObject Excel.Application

    $WorkSheet = $Excel.Worksheets.Item($WorkSheetName)

    $Range = $WorkSheet.Range($VolumeIDColumn)

    It turns out I was missing one completely.

    $Target = $Range.Find($SharedDrive)

    After adding the following to my End process of the script

            while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Target)){}
            while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Range)){}
            while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet)){}
            while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)){}

    I see that like JRV said, after about 10 seconds or so Excel properly terminates. As this works for me on both x86 and x64 versions of PowerShell I consider this to be resolved for myself.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Saturday, May 19, 2012 7:47 PM
  • Here is an alternate call which may be more convenient.  

    [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($object)

    It sets the reference count to zero without using a loop.  I had used it in the past in C# but forgot about it.  This is useful when you want to absolutely release the object.  Many times we only want to release the latest reference to it.  The assignments and releases in that case are usually paired in a function.  We wouldn’t want to release everything if other threads or functions were still using the object.  In this case we just want to kick Excel out of memory and this is one of the four convenient ways to do that.

    If there is any doubt about the existence of the object such as in a try/catch block you can wrap the call in another try/catch.  It will display an exception if the object is null.

    Example:

    # open Excel and force an exception then clean it up.
    $xl = New-Object –Com Excel.Application
    Remove-Variable testfile -ea 0
    Try{
          $wb =$xl.Workbooks.Open($testfile)
    }
    Catch{
         Write-Host "here"
    }
    Finally{
         try{
              Write-Host "Quit Excel" -fore green
              $xl.Quit()
              Write-Host 'remove wb' -fore green
              [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($wb)
              Write-Host 'remove xl' -fore green
              [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($xl)
         }
         catch{
              # display the exception on the console to show that it has been triggered.
              Write-Host $_ -fore blue -back yellow
              continue
         }
    }

    The above should work as a good demo of what is needed to prevent loss of objects and to prevent exceptions from not allowing us to remove Excel from memory.

    What is to be noted is that the code continues in the 'Finally' block even thought the 'Finally' block generates an exception.  Using 'continue' causes the code in the 'Finally' block to continue execution at the next line in teh 'Finlly' block so it will coninue removing items even if none of them exist.  Which ones exist depends on where in the 'Try' code the exception occurred.

    What is being demonstrated here is not how to catch an exception, but how to create cleanup code that will work even if an exception has occurred.  The exact design of this code can be as simple as the example or as complex as is needed to accomplish the goals of your project.

    n'est-ce pas?


    ¯\_(ツ)_/¯



    • Edited by jrv Saturday, May 19, 2012 10:31 PM
    Saturday, May 19, 2012 10:24 PM
  • Here is an alternate call which may be more convenient.  

    [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($object)

    It sets the reference count to zero without using a loop.  I had used it in the past in C# but forgot about it.  This is useful when you want to absolutely release the object.  Many times we only want to release the latest reference to it.  The assignments and releases in that case are usually paired in a function.  We wouldn’t want to release everything if other threads or functions were still using the object.  In this case we just want to kick Excel out of memory and this is one of the four convenient ways to do that.

    If there is any doubt about the existence of the object such as in a try/catch block you can wrap the call in another try/catch.  It will display an exception if the object is null.

    Example:

    # open Excel and force an exception then clean it up.
    $xl = New-Object –Com Excel.Application
    Remove-Variable testfile -ea 0
    Try{
          $wb =$xl.Workbooks.Open($testfile)
    }
    Catch{
         Write-Host "here"
    }
    Finally{
         try{
              Write-Host "Quit Excel" -fore green
              $xl.Quit()
              Write-Host 'remove wb' -fore green
              [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($wb)
              Write-Host 'remove xl' -fore green
              [void][System.Runtime.Interopservices.Marshal]::FinalReleaseComObject($xl)
         }
         catch{
              # display the exception on the console to show that it has been triggered.
              Write-Host $_ -fore blue -back yellow
              continue
         }
    }

    The above should work as a good demo of what is needed to prevent loss of objects and to prevent exceptions from not allowing us to remove Excel from memory.

    What is to be noted is that the code continues in the 'Finally' block even thought the 'Finally' block generates an exception.  Using 'continue' causes the code in the 'Finally' block to continue execution at the next line in teh 'Finlly' block so it will coninue removing items even if none of them exist.  Which ones exist depends on where in the 'Try' code the exception occurred.

    What is being demonstrated here is not how to catch an exception, but how to create cleanup code that will work even if an exception has occurred.  The exact design of this code can be as simple as the example or as complex as is needed to accomplish the goals of your project.

    n'est-ce pas?


    ¯\_(ツ)_/¯



    lovely!

    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Monday, May 21, 2012 7:33 PM
  • Jeffery et. al.

    We should also be able to use the master object, "$xl" or $excel" or whatever you called the appliation object, to detect all other object generated off of that one object.  I have had time to do this in PowerShell but it would then allow us to just send the main object to a function and have it find and release all related objects.  This would be much cleaner than any other method.


    ¯\_(ツ)_/¯

    Monday, May 21, 2012 7:42 PM
  • @jrv, by no means am I an expert or even close in this, but I'm using this page as a reference.

    http://msdn.microsoft.com/en-us/library/atxe881w(v=vs.110)

    When I attempt to use [System.Runtime.Interopservices.Marshal]::<<membername>>($Excel) there are few if any that actually return anything. Several throw errors that to me read the thing you want isn't there. Others seem to require additional information like a pointer or type but when I attempt to use the members that appear to get that information I get errors.

    I hope someone else can perhaps find something more useful.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    Monday, May 21, 2012 8:24 PM
  • @jrv, by no means am I an expert or even close in this, but I'm using this page as a reference.

    http://msdn.microsoft.com/en-us/library/atxe881w(v=vs.110)

    When I attempt to use [System.Runtime.Interopservices.Marshal]::<<membername>>($Excel) there are few if any that actually return anything. Several throw errors that to me read the thing you want isn't there. Others seem to require additional information like a pointer or type but when I attempt to use the members that appear to get that information I get errors.

    I hope someone else can perhaps find something more useful.


    Jeffrey S. Patton Assistant Director of IT School of Engineering Computing Services University of Kansas 1520 West 15th Street Lawrence, KS. 66045-7621 | http://patton-tech.com

    That is why I haven;t done this yet.  It cannot be easily done using tehat class.  We need to look depper intot the objects to see what teh underlying COM object is and if its instance is the one we are looking for.  I don't guarantee it is possible  although it should be.


    ¯\_(ツ)_/¯

    Monday, May 21, 2012 10:04 PM
  • I know this string has been closed for a while, but I just wanted to add a hearty 'AMEN!' to jrv's comment in case someone else runs into my issue:

    "I have been down this path dozens of times.  It works correctly when you do it correctly.  If anything is not correct then you get back bad info and Excel will not shutdown.

    If you open a corrupt document you need to cancle all messages in order to close and quit cleanly. "

    I just spent hours working on a problem.  Everything had been working fine, and for whatever reason (I was never able to figure out what happened), when I closed my file I would always end up with a prompt to Save, discard, cancel.  I was trying everything I could from all the suggestions in this post.  I even went back to code from several days before the problem started, and ended up with the same result, even though I had never seen it with that code.  Finally, I manually tranferred the contents of my .xlsx file to a brand new file and everything worked fine.  The key "if anything is not correct".

    In my case, it was not a case of Excel not shutting down, it was a case of requiring to answer a prompt that should not have been occurring.  I don't know how the file was 'corrupted', because all the data was still the same.  But when I created a new file, my problem disappeared.


    .:|:.:|:. tim

    Tuesday, January 15, 2013 5:09 PM
  • I know this string has been closed for a while, but I just wanted to add a hearty 'AMEN!' to jrv's comment in case someone else runs into my issue:

    "I have been down this path dozens of times.  It works correctly when you do it correctly.  If anything is not correct then you get back bad info and Excel will not shutdown.

    If you open a corrupt document you need to cancle all messages in order to close and quit cleanly. "

    I just spent hours working on a problem.  Everything had been working fine, and for whatever reason (I was never able to figure out what happened), when I closed my file I would always end up with a prompt to Save, discard, cancel.  I was trying everything I could from all the suggestions in this post.  I even went back to code from several days before the problem started, and ended up with the same result, even though I had never seen it with that code.  Finally, I manually tranferred the contents of my .xlsx file to a brand new file and everything worked fine.  The key "if anything is not correct".

    In my case, it was not a case of Excel not shutting down, it was a case of requiring to answer a prompt that should not have been occurring.  I don't know how the file was 'corrupted', because all the data was still the same.  But when I created a new file, my problem disappeared.


    .:|:.:|:. tim

    Yes - you need to disable all alerts before you open or create a document.

    That really has nothing to do with this thread.  If you still have issues please start a new topic.


    ¯\_(ツ)_/¯

    Tuesday, January 15, 2013 5:42 PM
  • Like Tim Cerling, I apologize for re-opening a long-closed thread, but (a) while the solution stated here solves my "Excel zombie" problem (b) my implementation of this solution creates an annoying powershell problem.

    In short: Excel exits - way cool - but when I list variables:

    ls variable:

    posh throws the following error:

    format-default : COM object that has been separated from its underlying RCW cannot be used.
        + CategoryInfo          : NotSpecified: (:) [format-default], InvalidComObjectException
        + FullyQualifiedErrorId : System.Runtime.InteropServices.InvalidComObjectException,Microsoft.PowerShell.Commands.FormatDefaultCommand

    The next two hyperlinks seem to say that ReleaseComObject is the spawn of Satan:

    http://blogs.msdn.com/b/visualstudio/archive/2010/03/01/marshal-releasecomobject-considered-dangerous.aspx

    http://stackoverflow.com/questions/10109782/com-object-that-has-been-separated-from-its-underlying-rcw-cannot-be-used

    precisely because ReleaseComObject can leave other programs (*cough*Powershell*cough*) with stale references.

    The problem seems to be that when I release a powershell variable using 'rv' the posh variable isn't available for subsequent use in ReleaseComObject; alternatively, if I release the com object using ReleaseComObject, posh knows that the reference is stale and throws an error when I run 'rv'.

    How does one get rid of both the com object and the posh variable? What silly n00b error am I committing?

    Here's the code that does the dirty work:

    function release($foo) {
    	while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($foo)) {}
    }
    
    function killExcel()
    {
    	$xl.displayalerts = $false
    	for ($i=1; $i -lt $wss.count; $i++) {
    		$wss.Item(1).Delete()
    	}
    	release $wss
    	$wb.close($false, $false, $false)
    	release $wb
    }
    
    function quit()
    {
    	killExcel
    	$xl.Quit()
    	release $xl
    	[gc]::collect()
    	[gc]::WaitForPendingFinalizers()
    	exit
    }

    where:

    $wb = $xl.Workbooks.Add()
    $wss = $wb.worksheets

    I see this behavior when I run this code using:

    . ./script
    

    When I run the code using ./script, the stale variables go away. Put differently, ls variable: works.

    This is with Windows 7, Office 2010 both up-to-date.






    • Edited by jhsnyder Tuesday, April 30, 2013 4:33 AM clarify invocation and identify software.
    Tuesday, April 30, 2013 3:51 AM
  • No - more like Rod Serling I would say.

    Be kind and start a new thread and I will show you the way to do this.

    Note that if you Quit Excel it will quit.

    It only takes two lines to release all of Excel.


    ¯\_(ツ)_/¯

    Tuesday, April 30, 2013 4:59 AM