I have a VBA script which executes a macro in Excel and then saves and closes Excel. The macro executes successfully, but the Excel sometimes displays a "Save" dialog box, and does not close.
To address this issue (short-term fix), I would like to create a PowerShell script which finds the open version of Excel and close it gracefully. Per https://stackoverflow.com/a/56454310/11262633, I can find/kill the session with get-process excel and stop-process, but it causes Excel to create a recovery session that I later have to clean up.
My VBA script successfully executed the code below:
MacroName = 'Update_All'
do while true
Set xl = CreateObject("Excel.Application")
xl.visible = True
xl.displayalerts = False
Set wb = xl.WorkBooks.open('Path/to/workbook')
xl.Run MacroName
if err.number <> 0 Then
Write-Host "Error running macro " & MacroName
exit do
end if
wb.Save
xl.quit
if err.number <> 0 Then
Write-Host "Error quitting Excel"
exit do
end if
RETCODE = 0
exit do
Loop
Intermittently, Excel remains open and displays a "Save" dialog box.
Below is my short-term fix, written in PowerShell. When the "Save" dialog box is open, this code does not find the needed workbook:
$excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")
$excel.DisplayAlerts = $false
$wbs_to_close = $excel.Workbooks | where-object { $_.Name -like 'Test Excel Report*'}
if ($wbs_to_close){
foreach ($wb in $wbs_to_close){
Write-Host "Closing $_.name"
$_.Close()
}
}
If I close the "Save" dialog box and run the script, PowerShell finds the workbook, but freezes when attempting to close it.
My theories: the automation object is not properly communicating with Excel, or I am not interacting with it properly.
I'd very much appreciate any help which enables my scripts to better interact with Excel.