Excel in PowerShell - do I really have a valid session?

Viewed 77

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.

0 Answers
Related