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.

Hi,

Try

[System.GC]::Collect()

after releasing the COM objects.

Bill

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'}

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

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

• Edited by Bill_StewartModerator Thursday, May 17, 2012 9:49 PM Typo

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.

@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.

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.

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.

@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

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

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.

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

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.

.

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:

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?

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?

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.

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.

No, I always open the workbook and it looks fine.

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.

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

I think I got it. The following works and leaves nothing in memory. Notice how I release all Excel objects:

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.

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.

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

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?

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

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.

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.

zx38 - don't get mewrong. 

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.

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 wouldnt 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?