excelpowershelluser-interfacecomrcw

Can't get all excel processes to stop when closing through Powershell


With this code, I am opening excel(with visible = false so the user cannot see it), writing to a workbook, and then either opening excel(making it visible) after the script ends or closing it completely without saving. When I save excel, leave it open, end the script, and then manually close excel later, there are no background processes in the task manager. However, when I close excel with the script, it remains in the task manager.

Here is how I start excel:

    $script:excel = new-object -ComObject excel.application # create excel object
    $excel.visible = $false # hide excel window
    $script:workbook = $excel.Workbooks.Add() # add excel file
    $script:ws1 = $workbook.Worksheets.Item(1) # create new sheet

Here is how I close it:

        [gc]::Collect()
        [gc]::WaitForPendingFinalizers()
        if ($script:closeOnX) {
            #only do this if not keeping excel open
            Write-Host "Closing Excel"
            $excel.Quit()
        }
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

closeOnX is just a flag so it only actually closes the excel app on certain occasions. The rest is executed each time the script ends.

When I end the script and close excel at the same time, I want only the current excel process to close (which is why I don't want to stop-process) and not close other workbooks that the user may be working on.

When I end the script, save and open the excel, I want all the processes to be gone when the user manually closes excel. (This is working)


Solution

  • 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:

    # 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: