I couldn't find anything online about how to close the ComObject after making it in constrained mode. The normal procedure is to use $com.Quit(), however that is not allowed in constrained mode. Also, [System.Runtime.InteropServices.Marshal]::ReleaseComObject() is not allowed in constrained mode.
It was suggested I pose a new question by mklement0 in the following thread: Can't get all excel processes to stop when closing through Powershell
Let me offers a more PowerShell-idiomatic solution that should also perform better:
# Capture the PIDs (process IDs) of all *preexisting* Excel processes
# In PowerShell 7+, you can simplify to (see bottom section for a discussion):
# $exelPidsBefore = (Get-Process -ErrorAction Ignore Excel).Id ?? @()
$excelPidsBefore = @(
Get-Process -ErrorAction Ignore Excel | Select-Object -ExpandProperty Id
)
# Perform the desired programmatic Excel operations:
# Create an Excel COM Automation object, which
# invariably creates a *new* Excel process.
$excel = New-Object -ComObject Excel.Application
# Determine the PID of the just-launched new Excel process.
# Note: This assumes that no *other* processes on your system have
# simultaneously launched Excel processes (which seems unlikely).
$excelComPid =
Compare-Object -PassThru $excelPidsBefore (Get-Process -ErrorAction Ignore Excel).Id
# Work with the Excel Automation object.
# ...
# Clean up by terminating the COM-created Excel process.
# NOTE:
# * As stated in your question, you would normally use $excel.Quit()
# but given that your running in *constrained language mode*, you
# are not permitted to invoke *methods*.
Stop-Process -Id $excelComPid
As an - entirely optional - aside:
Normally, Get-Process -ErrorAction Ignore Excel | Select-Object -ExpandProperty Id
can be simplified to (Get-Process -ErrorAction Ignore Excel).Id
(as is done later), thanks to member-access enumeration - a member-enumeration expression is not only more concise, but also more efficient.
The reason that won't work as intended in this case is that if there is no output from GetProcess
, $null
is returned, which @()
then wraps into an array, resulting in a single-element array whose only element is $null
, and passing such an array to Compare-Object
fails.
The behavior of member-access enumeration in the no-input and single-input-object cases is unfortunate, but something that cannot be fixed, so as not to break backward compatibility.
In PowerShell (Core) 7+ the problem could be worked around as follows, using ??
, the null-coalescing operator:
$exelPidsBefore = (Get-Process -ErrorAction Ignore Excel).Id ?? @()