How to Close excel ComObject in Powershell Constrained mode

Question

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

Nimesh

Answer 1

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.

  • See GitHub issue #6802 for a discussion.

• In PowerShell (Core) 7+ the problem could be worked around as follows, using ??, the null-coalescing operator:

  $exelPidsBefore = (Get-Process -ErrorAction Ignore Excel).Id ?? @()
  

Nicholson

Ohh yeah way better than my method. Depending on how long the script takes, the user could have opened more excel processes between starting and finishing. – Nimesh

Answer 2

I came up with a way to do this since I posed the question in the other thread. It's not as tidy as just calling .Quit(), but it works. I get a list of any open procs for the application (excel in this instance) and store it in an array before i call my new ComObject. Then, I close any open pids not in the initial array after i save my file.

Example:

$StartOpenExcel = get-process excel | ForEach-Object {$_.id} #make an array with the pids;
​$excel=New-Object -ComObject excel.application;
{...do something here }
$workbook.SaveAs($fileName,51);#save the excel doc
Get-Process excel | ForEach-Object{
    if($StartOpenExcel -contains $_.id -eq $false){
        kill -Id $_.Id;
    }
 }
II $fileName; #open the excel

Nimesh

Thanks for sharing that; the code can be streamlined a bit, as shown in my answer. In general, note that ; is never required to terminate a statement in PowerShell (you only need if you want to place multiple statements on the same line). – Nicholson