I have a script that opens a set of Excel workbooks in a single Excel instance and updates connections and pivot caches. The end user reports that they usually open all the workbooks are refresh connections in parallel instead of in series. It looks like parallel processing is possible with PowerShell but so far I’m unable to get Excel Workbooks to open using the script block and Start-Job method. All that happens is that the Excel instance is launched and the job runs forever without completing.

$filePath = "C:testpath"

#Array: FileName, SaveAs
$files = @()
$files += , @("tst1.xlsx", $false)
$files += , @("tst2.xlsx", $false)

Write-output "$(Get-Date -Format yyyy-MM-dd.hh:mm:ss): Starting loop"
$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $true

ForEach($file in $files) {
    
    # Define what each job does
    $ScriptBlock = {

        param([Object[]]$x1,
              [string]$filePath,
              [array]$file) 

        $wb = $x1.workbooks.Open("$filePath$($file[0])")
            
        ForEach ($cache in $wb.PivotCaches()) {
            #Refresh this cache
            $cache.Refresh()
        }
    }
        
    # Execute the jobs in parallel
    Start-Job $ScriptBlock -ArgumentList $x1, 
                                         $filePath,
                                         $file
}

Get-Job
 
# Wait for it all to complete
While (Get-Job -State "Running") {
    # Pause for 10 seconds
    Start-Sleep 10
}
 
# Getting the information back from the jobs
Get-Job | Receive-Job

Write-output "$(Get-Date -Format yyyy-MM-dd.hh:mm:ss):Loop complete"

All of this works just fine without parallel processing:

$filePath = "C:testpath"

#Array: FileName, SaveAs
$files = @()
$files += , @("tst1.xlsx", $false)
$files += , @("tst2.xlsx", $false)

Write-output "$(Get-Date -Format yyyy-MM-dd.hh:mm:ss): Starting loop"
$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $true

ForEach($file in $files) {
    
    $wb = $x1.workbooks.Open("$filePath$($file[0])")
            
    ForEach ($cache in $wb.PivotCaches()) {
        #Refresh this cache
        $cache.Refresh()
    }
}

Clues on how to get Excel to launch the workbook within the script block? I put a code break in on the $wb = $x1.workbooks.Open("$filePath$($file[0])") and the code doesn’t stop, it just runs forever. This is the output returned: