Refreshing Excel connections with Powershell

PowerShell Help
1

I have a couple questions about a script I am working on (see below). In short, the script updates connections in all Excel files in a given directory, with a couple exceptions.

First, it seems that when Powershell executes a command to open, refresh, save, etc a workbook, it doesn’t necessarily wait for that task to complete before moving on to the next task. I have some Start-Sleep commands inserted to add delays, but it doesn’t seem to work across the board, as some files are still throwing some errors. At the least, Start-Sleep needs to be set to a high delay to accommodate the larger files, which adds a lot of unnecessary delay to the smaller files, subsequently causing the script to take a lot longer to run. For the refresh task, I added “While ($ExcelWorkbook.ODBCConnection.Refreshing) {Start-Sleep -Seconds 1}”, which might work for other tasks, but I’m not sure what the syntax would need to be for other tasks, such as opening, saving, etc.

Secondly, I occasionally see some errors like the following, but I’m not entirely sure why. They don’t always show up every time I run the script, and are not always on the same files. I’m guessing some of them might be due to Powershell performing a task before Excel has finished the previous task, but I’m not sure, as I am less familiar with working with Excel in Powershell. If it makes any difference, they seem to start showing up after the script has gone through a few dozen files successfully, so maybe something is being left open from each file and taxing the system?

Thanks in advance for the help,

Andrew

Method invocation failed because [System.__ComObject] does not contain a method named 'Save'.
At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:44 char:9
+         $ExcelWorkbook.Save()
+         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Save:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

You cannot call a method on a null-valued expression.
At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:59 char:9
+         $Excel.Workbooks.Close()
+         ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:44 char:9
+         $ExcelWorkbook.Save()
+         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Exception calling "Quit" with "0" argument(s): "Call was rejected by callee. (Exception from HRESULT: 0x80010001 
(RPC_E_CALL_REJECTED))"
At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:63 char:5
+     $Excel.Quit()
+     ~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException

# Set library path variable
    $LibraryPath = “\\server\path”

# Open Excel
    $Excel = New-Object -ComObject Excel.Application
    Start-Sleep -Seconds 5

# Set visibility of Excel and alerts
    $Excel.Visible = $False
    $Excel.DisplayAlerts = $False

# Get Excel files
    $AllExcelFiles = Get-ChildItem $LibraryPath | Where {($_.Name -like "*.xls*") -and ($_.Name -notlike "TEST*")}

# Settings for PDF Export
    $ExportPDFList = Join-Path -Path $LibraryPath -ChildPath "PDF Exports\~ExportPDFList.txt"
    $xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]

# Refresh each Excel file in $LibraryPath
    foreach ($File in $AllExcelFiles)
        {
        Write-Host $File.Name -ForegroundColor Green
        $WorkbookPath = $File.Fullname
    
        # Open the workbook
        $Timestamp = Get-Date -DisplayHint Time
        Write-Host "   "$($Timestamp) "- Opening..."
        $ExcelWorkbook = $Excel.Workbooks.Open($WorkbookPath)
        Start-Sleep -Seconds 10

        # Refresh All the pivot tables data
        $Timestamp = Get-Date -DisplayHint Time
        Write-Host "   "$Timestamp "- Refreshing..."
        $ExcelWorkbook.RefreshAll()
        While ($ExcelWorkbook.ODBCConnection.Refreshing) {Start-Sleep -Seconds 1}

        # Save the file
        $Timestamp = Get-Date -DisplayHint Time
        Write-Host "   "$Timestamp "- Saving..."
        $ExcelWorkbook.Save()

        # Export PDF
        $Export = Select-String -Path $ExportPDFList -Pattern $File.Name
        if ($Export -ne $null)
            {
            $Timestamp = Get-Date -DisplayHint Time
            Write-Host "   "$Timestamp "- Exporting PDF..."
            $PDFPath = Join-Path -Path $LibraryPath -ChildPath ("PDF Exports\$($File.BaseName)" + ".pdf")
            $ExcelWorkbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $PDFPath)
            }

        # Close file
        $Timestamp = Get-Date -DisplayHint Time
        Write-Host "   "$Timestamp "- Closing..."
        $Excel.Workbooks.Close()
        }

# Close Excel
    $Excel.Quit()
    Get-Process -Name "*Excel*" | Stop-Process

#Release Excel object from memory
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
2

Your assumptions are more or less correct. Unfortunately, that COM object is -ancient-, and wrapping it in .NET in 2000 or so didn’t do much to make it less weird. It -ought- to implement callbacks for long-running tasks, but doesn’t, and the whole thing is entirely deprecated so we’re not likely to see any improvements. You’re unfortunately stuck with whatever goofy workarounds you can come up with to make it less ornery.