Powershell, close Excel

Question

I have the following code and it works perfectly except it's not closing Excel properly. It's leaving an Excel process running.

Is there a way to close Excel properly without killing the process?

Since i'm using other Excel files while running this script i can not kill all active Excel processes.

I think i tried everything i found online.

$WorkDir = "D:\Test\QR_ES\RG_Temp"
$BGDir = "D:\Test\QR_ES\3_BG"
$File = "D:\Test\QR_ES\4_Adr_Excel\KD_eMail.xlsx"
$SentDir = "D:\Test\QR_ES\RG_Temp\Sent\Dunning"

chdir $WorkDir

$firstPageList = Get-ChildItem "$WorkDir\1*.pdf" -File -Name

ForEach ($firstPage in $firstPageList)
{
$secondPage = "$BGDir\BG_RG.pdf"

$output = "Dunn-$firstPage"

invoke-command {pdftk $firstPage background $secondPage output $output}}

del 1*.pdf

gci $WorkDir\Dunn-*.pdf | rename-item -newname {$_.Name.Substring(5)} -Force


$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Workbook = $Excel.workbooks.open($file)

$DunnList = Get-ChildItem "$WorkDir\1*.pdf" -File -Name

ForEach ($Dunn in $DunnList)
{
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item("KD_eMail")
$Range = $Worksheet.Range("A1").EntireColumn
$DunnSearch = $Dunn.Substring(0,5)
$SearchString = $DunnSearch
$Search = $Range.find($SearchString)
$Recipient = $Worksheet.Cells.Item($Search.Row, $Search.Column + 1)

$Msg = "<span style='font-family:Calibri;font-size:12pt;'>Test</span>"

$Outlook = New-Object -ComObject Outlook.Application
$namespace = $Outlook.GetNameSpace("MAPI")
$namespace.Logon($null, $null, $false, $true)
$EmailFrom = ('test@test.com')
$account = $outlook.Session.Accounts.Item($EmailFrom)
$Mail = $Outlook.CreateItem(0)
$Mail.HTMLBody = $Msg
$Mail.Subject = "OP - $SearchString"

$Mail.To = $Recipient

function Invoke-SetProperty {
    param(
        [__ComObject] $Object,
        [String] $Property,
        $Value        
    )
    [Void] $Object.GetType().InvokeMember($Property,"SetProperty",$NULL,$Object,$Value)
   }
Invoke-SetProperty -Object $mail -Property "SendUsingAccount" -Value $account

$Mail.Attachments.Add("$WorkDir\$Dunn")
$Mail.Save()
$Mail.close(1)
$Mail.Send()}}

$workbook.close($false)

$Excel.Quit()

chdir $WorkDir

del 1*.pdf

Try releasing the reference of your com object after quitting. See [this answer](https://stackoverflow.com/a/52597065/934946). I think this should resolve your issue. Also, indirectly related to the more generic Excel with Powershell topic, take a look at [ImportExcel](https://www.powershellgallery.com/packages/ImportExcel) module by Doug Finke if you need to do more complex things with Excel at some point. It will take a lot of the pain away. — Sage Pourpre, Aug 23 '21 at 03:52

score 0 · Answer 1 · answered Aug 23 '21 at 06:18

0

See this post: https://stackoverflow.com/a/35955339/5329137 which is not accepted as an answer, but I believe is the full, correct way to close Excel.

answered Aug 23 '21 at 06:18

Kory Gill

6,993
1
25
33

score 0 · Accepted Answer · answered Aug 23 '21 at 17:25

0

This is what did it for me:

$FilePID = (Get-Process -name Excel | Where-Object { $_.MainWindowTitle -like 'FileName.xlsx*' }).Id

$Workbook.Save()
$Workbook.close($false)

Stop-Process $FilePID

answered Aug 23 '21 at 17:25

ASD

55
2
10

Good to know you solved it. So, mark your answer and the accepted one. – postanote Aug 23 '21 at 17:55

score 0 · Answer 3 · answered Nov 03 '22 at 15:02

Elaborating on @ASD's answer, since the MainWindowTitle doesn't (always) include the file suffix (.xlsx) you may have to strip that when comparing it to the filename. I'm using -replace to use a Regex match of everything before the last dot.

$excelPID = (Get-Process -name Excel | Where-Object { $_.MainWindowTitle -eq $fileName -replace '\.[^.]*$', '' }).Id
$workbook.Close()
Stop-Process $excelPID

Powershell, close Excel

3 Answers3