PowerShell script to open excel, wait for power query to refresh, and then save the file

Question

I have an Excel that is using Power Query to get data from a API. What I would like to do is have this data update every day without having to open the excel myself. So I enabled the setting within excel to Refresh data when opening the file.

So I am trying to create a PowerShell script which open the excel, waits for the query to refresh, and then saves the excel. However I cant get it to wait update the query has refreshed before saving and closing.

code:

$Excel = New-Object -COM "Excel.Application"
$Excel.Visible = $true
$Workbook = $Excel.Workbooks.Open("G:\...\jmp-main-2020-07-17.xlsx")

While (($Workbook.Sheets | ForEach-Object {$_.QueryTables | ForEach-Object {if($_.QueryTable.Refreshing){$true}}}))
{
    Start-Sleep -Seconds 1
}

$Excel.Save()
$Excel.Close()

We haven't heard from you.. Did any of the given answers solve your problem? If so, please consider accepting it by clicking ✓ icon on the left. This will help others with a similar question finding it more easily. — Theo

Theo Theo · Accepted Answer · 2020-07-22T19:30:12

I think your while loop is wrong. You should probably loop over the worksheets in the workbook and for each of them loop over the QueryTables. Then enter a while loop to wait until the Refreshing property turns $false

foreach ($sheet in $Workbook.Sheets) {
    $sheet.QueryTables | ForEach-Object {
        while ($_.QueryTable.Refreshing) {
            Start-Sleep -Seconds 1
        }
    }
}

As aside: you should clear the COM object you have created after finishing with them to free memory:

$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

PowerShell script to open excel, wait for power query to refresh, and then save the file

2 Answers