Windows PowerShell Tip of the Week

  • Article

Here’s a quick tip on working with Windows PowerShell. These are published every week for as long as we can come up with new tips. If you have a tip you’d like us to share or a question about how to do something, let us know.

Find more tips in the Windows PowerShell Tip of the Week archive.

Getting Rid of a COM Object (Once and For All)

Sometimes Windows PowerShell just doesn’t know when to quit.

That’s especially true when you’re working with COM objects (most notably Microsoft Excel). What do we mean when we say that PowerShell “just doesn’t know when to quit”? Well, consider the following script, a script that:

  1. Starts Microsoft Excel

  2. Makes Excel visible on screen.

  3. Pauses for 5 seconds.

  4. Terminates Excel.

Here’s what the code looks like:

$x = New-Object -com Excel.Application
$x.Visible = $True
Start-Sleep 5
$x.Quit()

So what happens when you run this script? Well, Excel appears on screen, then 5 seconds later it disappears. What’s the big deal?

Well, the big deal is that Excel seems to be gone; unfortunately, though, Excel might be gone but it’s definitely not been forgotten. In fact, the application is still chugging away in the background; you just can’t see it onscreen any more. (Although if you rerun the command $x.Visible = $True you will be able to see it again.) Let’s assume that we have no other instances of Excel running and we fire off our sample script; that means that, for a few seconds, we’ll have a single instance of Excel running. Once that instance terminates we would assume that we would no longer have any instances of Excel running on the computer. How can we verify that assumption? One way is to run the following command as soon as the script is finished:

Get-Process -name Excel

In theory, this should return nothing; that’s because, in theory, we no longer have any instances of Excel running. As you probably know, however, theory and reality are often two very different things:

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    166       6     4892      10100   193     0.19   4012 EXCEL

Uh-oh. As you can see, calling the Quit method turned out to be a bit like sweeping dirt under the rug: we didn’t actually get rid of the problem, we just hid it.

The Scripting Guys hardly qualify as experts on the .NET Framework; however, as near as we can tell, the .NET Framework is to blame for this unexpected behavior. Whenever you call a COM object from the common language runtime (which happens to be the very thing you do when you call a COM object from Windows PowerShell), that COM object is wrapped in a “runtime callable wrapper,” and a reference count is incremented; that reference count helps the CLR (common language runtime) keep track of which COM objects are running, as well as how many COM objects are running. When you start Excel from within Windows PowerShell, Excel gets packaged up in a runtime callable wrapper, and the reference count is incremented to 1.

That’s fine, except for one thing: when you call the Quit method and terminate Excel, the CLR’s reference count does not get decremented (that is, it doesn’t get reset back to 0). And because the reference count is not 0, the CLR maintains its hold on the COM object: among other things, that means that our object reference ($x) is still valid and that the Excel.exe process continues to run. And that’s definitely not a good thing; after all, if we wanted Excel to keep running we probably wouldn’t have called the Quit method in the first place.

So how do we solve this little problem? Fortunately, we can do that simply by adding a single line of code to the end of our script:

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

What we’re doing here is delving into the .NET Framework (in particular, the System.Runtime.Interopservices.Marshal class) and calling the ReleaseComObject method, passing ReleaseComObject the object reference ($x) to our instance of Excel. What does ReleaseComObject do? ReleaseComObject does just one thing: it decrements the reference count for the object in question. In this case, that means it’s going to change the reference count for our instance of Excel from 1 to 0. And that is a good thing: once the reference count reaches 0 the CLR releases its hold on the object and the process terminates. (And this time it really does terminate.)

Give it a try and see for yourself. To verify that the Excel process really did terminate, simply re-run the command Get-Process –name Excel. You should get back something similar to this:

Get-Process : Cannot find a process with the name 'Excel'. Verify the process name and call the cmdlet again.
At line:1 char:12
+ Get-Process  <<<< -name Excel

In other words, there aren’t any processes with the name Excel running on the computer. And that’s good; after all, there aren’t supposed to be any processes with the name Excel running on the computer.

Incidentally, while you’re at it you might want to delete the object reference $x; that way you won’t run into any problems by trying to use an object reference that’s no longer valid. How can you delete an object reference? By calling the Remove-Variable cmdlet, of course:

Remove-Variable x

Note. That command is correct; when using the Remove-Variable cmdlet you specify only the variable name itself (x), leaving off the $.

Here’s what our new, and much-improved, script looks like in full:

$x = New-Object -com Excel.Application
$x.Visible = $True
Start-Sleep 5
$x.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($x)
Remove-Variable x

See you next week.