1

I am stuck on how to completely quit Excel when working with files in Powershell. I have a find and replace script that searches through workbooks and sheets, once completed, I notice that there is still a single process running in task manager as shown from image below: enter image description here

Here is the script:

Function Get-ExcelFunction () {
   
    $rootDir = Get-ChildItem $dirPath -Filter *.xlsx -Recurse

    ForEach ($dir in $rootDir) { 
        $Excel = New-Object -ComObject Excel.Application
        $Excel.visible = $false
        $Workbook = $Excel.workbooks.open($dir.FullName)

        ForEach ($sheet in $Workbook.Worksheets) {
            $Range = $sheet.Range("A1:EZ800").EntireColumn #Range of Cells to look at
            $Search = $Range.find($oldString)
            if ($null -ne $Search) {
                $FirstAddress = $search.Address
                do {
                    $Search.value() = $Search.value().Replace($oldString, $newString) #String to Update
                    $Search = $Range.FindNext($Search)
                } while ( $null -ne $Search -and $Search.Address -ne $FirstAddress) 
                $sheetName = $sheet.Name #Grab the name of the sheet that is being updated
                (Join-Path $dir.Directory $dir.BaseName) + "($sheetName)" + $dir.Extension >> $filePathName #Write the path to where the file was changed to a change log file
            }
        }
        
        $Workbook.Save()
        $Workbook.Close()
        $Excel.quit()

        # Relinquish references to *all* Excel objects.
        Remove-Variable -Name Excel, Workbook, sheet

        # With all references released, running the garbage collector
        # should now release the COM objects and terminate Excel
        # shortly after.
        [GC]::Collect()
    }

Prior to adding:

 Remove-Variable -Name Excel, Workbook, sheet
 [GC]::Collect()

I would have multiple processes still remaining, so I am wondering why my code is not completing all the Excel processes?

taraloca
  • 9,077
  • 9
  • 44
  • 77
  • Releasing references to COM objects for out-of-process COM servers such as `Excel.Application` requires calling their `.Quit()` method first. However, the process doesn't exit until all references to the application object _as well as to elements of its document model_ have been released. While `Remove-Variable` is one way to do this, it requires a lot of discipline. A simpler and more robust approach is to execute all COM operations in a _child scope_ (`& { ... }`) so that when its variables go out of scope, releasing happens automatically. – mklement0 Aug 11 '23 at 16:28
  • The approach is detailed in [this answer](https://stackoverflow.com/a/55423449/45375). If this solves your problem, we can close this question as a duplicate. – mklement0 Aug 11 '23 at 16:29
  • 1
    I did see this answer and just couldn't wrap my head around the child scope creation. I will read up on how to create one and you can mark this as a duplicate if that answer is a good one. Thanks. – taraloca Aug 11 '23 at 16:40
  • I've updated the [linked answer](https://stackoverflow.com/a/55423449/45375) with a summary (tl;dr) section, which hopefully clarifies the approach. (You accidentally posted your follow-up question there to a different answer). I hope this clarifies things; let us know if it doesn't. – mklement0 Aug 12 '23 at 00:38

0 Answers0