Guides · Excel automation

Excel VBA RefreshAll: Wait for Power Query and Catch a Failed Refresh

Published 7 October 2026 · Tested with Microsoft 365 Excel 16.0 (build 20430, 64-bit), Windows 11 Pro, Excel started from PowerShell 7.6.6

A report macro calls ThisWorkbook.RefreshAll, then saves. Most days the file is right. Some days it holds yesterday's numbers, and nothing failed. This guide reproduces both ways that happens with a Power Query table, and replaces the one line with a function that waits, notices a failed refresh, and lets the caller save only when the data is new.

Before you start

• Windows with Excel desktop and a workbook whose data comes from Power Query (or another OLE DB query) loaded into a table on a sheet.
• A trusted workbook. The functions below are plain VBA in a standard module; they run from a button, from another macro, or from a script that calls Application.Run.

The test workbook

Everything below was measured on a synthetic workbook: a Power Query query named Sales reads a local CSV of invented sales and loads it into a table on the sheet Data; the sheet Report has =ROWS(Sales[Amount]) and =SUM(Sales[Amount]). The query table has background refresh on. The workbook was saved with 200,000 rows. Before each run the CSV grew to 250,000 rows, so a correct refresh must end with 250,000. The macros were run from PowerShell through Application.Run with alerts off, the way a scheduled report runs, and the workbook was saved right after the macro returned.

Two ways RefreshAll leaves old data

1. It does not wait. Microsoft documents that objects “that have the BackgroundQuery property set to True are refreshed in the background” (Microsoft Learn, Workbook.RefreshAll). The call returns, the macro carries on, and a save in the next line writes the old rows:

' The usual macro
ThisWorkbook.RefreshAll
' ...then the report is saved

' Test run (output of our test script)
macro: rows right after RefreshAll: 200000
saved copy right away
20 s later, open workbook: rows 250000
saved copy: rows 200000, Report!B1 = 200000

Twenty seconds later the open workbook did show 250,000 rows. The saved file never did.

2. A failed refresh says nothing. With the CSV moved away, we tried three ways to refresh the same table:

WorkbookConnection.Refresh error: 0x800A03EC
QueryTable.Refresh(False) error: [DataSource.NotFound] File or Folder: Could not find file '…\data.csv'.
RefreshAll: no error
rows now: 200000

RefreshAll raised no error and the table kept its 200,000 old rows. WorkbookConnection.Refresh raised only Excel's generic 0x800A03EC. QueryTable.Refresh with BackgroundQuery set to False raised Power Query's own message, the one you need in a log. Switching background refresh off and calling Application.CalculateUntilAsyncQueriesDone (“Runs all pending queries to OLEDB and OLAP data sources”, Microsoft Learn) fixed the first problem in our test, but not the second: the missing file still returned OK.

The code

Add a standard module named Report with these two functions. RefreshAndCheck refreshes every query table in the workbook, one at a time and in the foreground, so a failure raises an error with its real message; then it checks a minimum row count you choose. RefreshThenSave is the entry point for a scheduled run: it saves only when everything passed, and returns the result as text instead of raising an error, because an unhandled error in a hidden Excel opens a dialog that nobody will click.

Option Explicit

' Refresh every query table in the workbook, one at a time and in the foreground,
' so that a failed refresh raises an error instead of leaving the old data in place.
' MinRows: the fewest rows any refreshed table may have (a report-specific check).
' Returns "OK: ..." or "FAILED: ...": the caller decides whether to save.
Public Function RefreshAndCheck(Optional ByVal MinRows As Long = 1) As String
    Dim ws As Worksheet, lo As ListObject, current As String, summary As String
    On Error GoTo Failed

    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            If lo.SourceType = xlSrcQuery Then
                current = lo.Name
                lo.QueryTable.Refresh BackgroundQuery:=False
                If lo.ListRows.Count < MinRows Then Err.Raise vbObjectError + 1, , _
                    "has " & lo.ListRows.Count & " rows, expected at least " & MinRows
                summary = summary & " " & lo.Name & "=" & lo.ListRows.Count
            End If
        Next lo
    Next ws
    Application.Calculate

    If summary = "" Then Err.Raise vbObjectError + 2, , "no query tables found"
    RefreshAndCheck = "OK: rows" & summary
    Exit Function

Failed:
    RefreshAndCheck = "FAILED: " & current & ": " & Err.Description
End Function

' Entry point for a scheduled run: save only when every refresh succeeded and passed
' its checks. Returns the result as text; it never shows a dialog or raises an error.
Public Function RefreshThenSave(Optional ByVal MinRows As Long = 1) As String
    Dim result As String
    result = RefreshAndCheck(MinRows)
    If Left$(result, 3) = "OK:" Then
        On Error GoTo SaveFailed
        ThisWorkbook.Save
        result = result & "; saved"
    End If
    RefreshThenSave = result
    Exit Function
SaveFailed:
    RefreshThenSave = "FAILED: save: " & Err.Description
End Function

Call it from a button with MsgBox RefreshThenSave(MinRows:=1), or from a script with $excel.Run('Report.RefreshThenSave') and treat anything that does not start with OK: as a failed run. If the script also needs a time limit, run the macro the way Run an Excel Macro from PowerShell with a Timeout shows.

Results

Same workbook, same 250,000-row CSV, the file saved right after the macro returned (copied from the test runs; the CSV path is shortened):

The foreground refresh took about 3.7 seconds for 250,000 rows on our machine; RefreshAll returned after about 1.8 seconds, before the data arrived.

Limits

• Only query tables on sheets. The code refreshes tables loaded to a worksheet. Queries loaded only to the Data Model or kept as connections, and PivotTables built on them, need their own refresh; we did not test those.
• Alerts off. Results above are with DisplayAlerts off, as in a scheduled run. In an interactive Excel, Power Query may show its own error message as well.
• Do not rely on RefreshDate. Our first version compared OLEDBConnection.RefreshDate with the start of the run; for the Power Query connection, reading it raised “Application-defined or object-defined error”.
• A row count is a floor, not proof. It catches an empty or truncated load. Add the check your report really needs: the latest date in the data, a control total, an ID that must be present.
• It blocks Excel while it runs. Foreground refresh is the point; for long refreshes, give the calling script a time limit.

Just want the scripts? The Scripts Pack · €12 has the code of this guide and the other guides ready to run, with examples and tests. The code on this page stays free.

The code on this page was written for this guide and tested on the versions above. The workbook and its data are invented.