Excel spawned from powershell script not quitting

Question

We're migrating databases so I'm using powershell to modify the hundreds of excel files that reference to old DB instance to the new one. This is all fine and works as intended. The problem I have is that the Excel application will not exit when I'm done. The process will just hang around as a background process and I need to go into task manager to kill it. Not a huge deal but it is annoying. Here is my script. I'm on powershell v5 and Office 2016.

    param(
    [string]$search_root=$(throw "missing search root parameter"),
    [boolean]$test=$true
)
echo $search_root

$NAMEPOSTFIX = '-Updated'
$OLDCONN = 'Data Source=abc;'
$NEWCONN = 'Data Source=xyz;'

$filelist = Get-ChildItem -Path $search_root *.xls* -Recurse -Exclude '*Updated.*'
$Excel = New-Object -Com Excel.Application
$Excel.DisplayAlerts = $False

function update_con_xls{
    param($file)    
    $Workbook = $Excel.Workbooks.Open($file)
    foreach($con in $Workbook.Connections){
        if ($con.OLEDBConnection -ne $null){
            $con.OLEDBConnection.Connection = $con.OLEDBConnection.Connection.Replace($OLDCONN,$NEWCONN)
        }
        if ($con.ODBCConnection -ne $null){
            $con.ODBCConnection.Connection = $con.ODBCConnection.Connection.Replace($OLDCONN,$NEWCONN)
        }
    }
    $Workbook.Save()
    $Workbook.saved = $true
    $Excel.Workbooks.Close()
}

foreach ($file in $filelist) {
    echo $file
    if ($file.Extension -eq '.xls' -or $file.Extension -eq '.xlsx') {
        $newfile = ($file.DirectoryName + '\' + $file.BaseName + $NAMEPOSTFIX + $file.Extension)
        if($test){
            echo $test
            $newfile = ('C:\test\' + $file.Name) #for test run to copy coppy locally 
        }
        Copy-Item $file.FullName -Destination $newfile
        update_con_xls($newfile)
    }
}
$Excel.Quit()
$Excel = $null

See https://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run/35955339#35955339 — Kory Gill, Mar 31 '18 at 00:33

f14284 · Answer 1 · 2018-03-30T21:33:05.410

0

Maybe change:

$Excel.Workbooks.Close()

to :

$Excel= New-Object -ComObject Excel.Application;

$Workbook = $Excel.Workbooks.Open($file);
$Workbook.Save();
$Workbook.Close(); # <--- try

$Excel.Quit();
Remove-Variable -Name Excel;

I do not see anything here that could close your ODB and OLEDb connection trying to add:

$con.OLEDBConnection.Connection.Close();
$con.ODBCConnection.Connection.Close();

or

$con.OLEDBConnection.Close();
$con.ODBCConnection.Close();

after your work is finished.

edited Mar 30 '18 at 21:33

answered Mar 30 '18 at 21:13

f14284

141
4

No that didn't change anything. Also the connections are never opened. I'm just changing the property of what it points to. I can even create the $Excel object then immediately close and remove the variable and it still stays in running memory. – George Rainone Mar 30 '18 at 22:54
ok, try $workbook.SaveAs($outputfile); , because Save() function probably block ? – f14284 Mar 30 '18 at 23:02
this work for me: $workbook.SaveAs($outputfile); $workbook.Close(); $excel.Quit(); Remove-Variable -Name excel; – f14284 Mar 30 '18 at 23:03
Also , have you try to put : $Excel.Visible = $false; – f14284 Mar 30 '18 at 23:11
1

From here https://stackoverflow.com/questions/27798567/powershell-excel-save-and-close-after-run: The documentation recommends using FinalReleaseComObject method to completely release the COM object and close the Excel process once in for all. – f14284 Mar 31 '18 at 00:02

Excel spawned from powershell script not quitting

1 Answers1