Make PowerShell Wait for Excel to Finish Refreshing Pivot Table

Question

So I developed a Powershell script to refresh about 40 large excel files and save them, and in this script I run an excel macro to pass the excel (ODBC) connection parameters and then delete them from the excel file after the refresh is done. My only problem is that (for 5 files of these 40 files) when the RefreshAll command is launched, Powershell does not wait for Excel to finish refreshing and passes through to the next command which is the macro that invalidates the connection, which causes the error "1004" in the macro (Cannot access to the excel connection while the file is still refreshing.)

After some research, I know that in Powershell v2, there's the concept of background jobs, but in my case, this is not an aside process, it belongs to the Excel process, so my Powershell shouldn't have to wait for Excel to quit to continue executing, on the contrary, I need the excel to stay open to continue the execution of Powershell.

I know also that the start-sleep command is not the best for my situation since there is no fix value of time for the refresh to end, every file has its time and also every period of the month has its time (the volume of data increases everyday until the month ends).

So my question is: Is it possible to make Powershell test if the Excel file has done refreshing its data, if yes it continues, else it waits even more?

beatcracker beatcracker · Accepted Answer · 2015-02-18T12:02:14

Shotgun approach:

Excel has Application.Ready property. It should indicate when Excel is ready for automation communication, but details are unclear. Try something like this:

$Excel = New-Object -ComObject Excel.Application

# Do your stuff here

# Wait for Excel to became ready
while (!$Excel.Ready)
{
    Start-Sleep -Seconds 1
}

 # Do other stuff

QueryTable has Refreshing property. Try this:

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('C:\Path\To\Workbook\Data.xlsx')

# Are any of the QueryTables refreshing?
# Don't have Excel right now, so expression below might need some tweaking
While (($Workbook.Sheets | ForEach-Object {$_.QueryTables | ForEach-Object {if($_.QueryTable.Refreshing){$true}}}))
{
    Start-Sleep -Seconds 1
}

ODBCConnection has Refreshing property.Try this:

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('C:\Path\To\Workbook\Data.xlsx')

# Is ODBCConnection refreshing?
# Don't have Excel right now, so expression below might need some tweaking
While ($Workbook.ODBCConnection.Refreshing)
{
    Start-Sleep -Seconds 1
}

Maybe those 5 files have PivotCache.BackgroundQuery, ODBCConnection.BackgroundQuery or QueryTable.BackgroundQuery property set to true?

Make PowerShell Wait for Excel to Finish Refreshing Pivot Table

2 Answers