tl;dr
Use the following idiom to ensure that all references to COM objects are released, which, in combination with calling $excel.Quit(), ensures that the Excel process (eventually) terminates:
& { # Create a temporary child scope.
$excel = New-Object -ComObject excel.application # create excel object
# ... work with the $excel object and its object model,
# using whatever local variables needed.
# You must *always* call .Quit(), otherwise the Excel process lingers
# for the entire OS user session.
$excel.Quit()
} # All variables created inside { ... } go out of scope
# when this block is exited, ensuring release of all COM objects.
For general guidance on how to release (Excel) COM objects, see the bottom section.
Background information:
$excel.Quit() is enough to eventually terminate the Excel process, but when that happens depends on when the garbage collector happens to run the next time.
Your attempt to explicitly release Excel with [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) is insufficient, because variables $script:workbook and $script:ws1 still have references to Excel COM objects that will not be released until the variables have gone out of scope and these references are eventually garbage collected.
Therefore, in order to speed up the release, you must release these references explicitly too, before running the garbage collector:
$script:excel = new-object -ComObject excel.application # create excel object
$script:workbook = $excel.Workbooks.Add() # add a workbook
$script:ws1 = $workbook.Worksheets.Item(1) # reference the 1st sheet
# ...
# You must *always* call .Quit(), otherwise the Excel process lingers
# for the entire OS user session.
$script.excel.Quit()
# Relinquish references to *all* Excel objects.
$script:excel = $script:workbook = $script:ws1 = $null
# Alternative:
# Remove-Variable -Scope Script excel, workbook, ws1
# With all references released, running the garbage collector
# should now release the COM objects and terminate Excel
# shortly after.
[GC]::Collect()
# Note that calling [GC]::WaitForPendingFinalizers() afterwards
# to wait for *completion* of the *doesn't work here*,
# because the CLR-managed RCWs (Runtime-Callable Wrappers for COM objects)
# do not guarantee deterministic release of the underlying COM objects.
Preferable alternative:
- Because manually clearing / removing all relevant variables is error-prone and cumbersome, you can instead automate the process, by creating all variables that reference COM objects locally in a temporary child scope, using
& { ... }:
# PREFERABLE ALTERNATIVE to the code above:
& { # Create a temporary child scope.
$excel = new-object -ComObject excel.application # create excel object
$workbook = $excel.Workbooks.Add() # add a workbook
$ws1 = $workbook.Worksheets.Item(1) # reference the 1st sheet
# You must *always* call .Quit(), otherwise the Excel process lingers
# for the entire OS users session.
$excel.Quit()
} # On exiting this block, $excel, $workbook, and $ws1
# go out of scope and release the COM objects when the
# garbage collector runs next.
# Run the garbage collector now.
# The Excel process should terminate shortly after.
[GC]::Collect()
Releasing (Excel) COM Objects:
ALWAYS call .Quit() - without it, the Excel process that is created behind the scenes is never terminated, not even when the PowerShell session ends (of course, it is terminated when the OS user session as a whole ends).
$excel.Quit() is usually all that is needed (unless global variables variables are used to store references to Excel objects), because with the script / function variables that reference COM objects going out of scope, the underlying COM objects are eventually released automatically too.
- However, it may take a - varying, unpredictable - while for the Excel process to actually terminate, depending on when the objects that that the gone-out-of-scope variables are garbage-collected.
If you want the COM objects to be released as quickly as possible:
You must release references to all COM objects you've stored in individual variables:
- Note that there is NO NEED for
[System.Runtime.InteropServices.Marshal]::ReleaseComObject() calls; because there is a simpler and more robust alternative:
- Either: Clear all variables referencing COM objects explicitly, by (see first code snippet above):
- either: setting them all to
$null.
- or: passing their names to
Remove-Variable
- Or, preferably: Release the references implicitly (see second code snippet above):
- Use variables that reference COM objects in a child scope, via a
& { ... } block, which means that the references will implicitly be released on leaving the child scope.
These approaches are not only simpler and more concise than calling [System.Runtime.InteropServices.Marshal]::ReleaseComObject(), but also prevent later attempts at accessing already-released COM objects.
Afterwards, call [GC]::Collect() to force instant garbage collection - but note that your code is blocked while the garbage collector runs (albeit usually only briefly) .
If you additionally want to make sure that releasing the COM objects has completed before you continue:
Note: There's probably rarely a need for this, because Excel usually releases resources when its .Quit() method is called, such as closing files it has open.
You can call [GC]::WaitForPendingFinalizers() after calling [GC]::Collect(), but it is likely not to work: The RCWs (runtime-callable wrappers) that manage access to the COM objects themselves being finalized does not guarantee release of the COM resources at that time; from the docs (emphasis added):
"When the reference count on the COM object becomes 0, the COM object is usually freed, although this depends on the COM object's implementation and is beyond the control of the runtime."
Indeed, in the case at hand the Excel process does not terminate before the [GC]::WaitForPendingFinalizers() call returns; that only happens within a second or so afterwards.
