How do I force refresh a PowerQuery with VBA?

Question

I'm trying to automate a workflow with a macro, but I can't seem to get the PowerQuery table to update with VBA.

Sub DepartmentalBudgetsMonthlyUpdate()
    Dim wb As Workbook
    Dim fName As String
    Dim folderPath As String
    Dim saveFolder As String
    Dim links As Variant
    Dim link As Variant
    Dim suffix As String
    Dim baseName As String
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim allDone As Boolean
    Dim lo As ListObject
    
    folderPath = "C:\Users\XXXX\Documents - Local\Departmental OpEx Reviews\New Opex Reviews\"
    saveFolder = "C:\Users\XXXX\Documents - Local\Departmental OpEx Reviews\08.25\"

    suffix = InputBox("Enter the month of the file:", "Month for File")
    
    If suffix = "" Then
        MsgBox "No month entered."
        Exit Sub
    End If

    If Dir(folderPath, vbDirectory) = "" Then
        MsgBox "The folder path does not exist: " & folderPath, vbCritical
        Exit Sub
    End If
    
    fName = Dir(folderPath & "*.xlsx")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do While fName <> ""
        Set wb = Workbooks.Open(folderPath & fName, UpdateLinks:=3, ReadOnly:=True)
        
        wb.RefreshAll
        
        For Each ws In wb.Worksheets
            For Each qt In ws.QueryTables
                qt.BackgroundQuery = False
                qt.Refresh BackgroundQuery:=False
            Next qt
        Next ws
        
        For Each ws In wb.Worksheets
            For Each lo In ws.ListObjects
                On Error Resume Next
                If Not lo.QueryTable Is Nothing Then
                    lo.QueryTable.BackgroundQuery = False
                    lo.QueryTable.Refresh
                End If
                On Error GoTo 0
            Next lo
        Next ws
        
        Do
            allDone = True
            For Each ws In wb.Worksheets
                For Each qt In ws.QueryTables
                    If qt.Refreshing Then allDone = False
                Next qt
                For Each lo In ws.ListObjects
                    If Not lo.QueryTable Is Nothing Then
                        If lo.QueryTable.Refreshing Then allDone = False
                    End If
                Next lo
            Next ws
            DoEvents
        Loop Until allDone
    
        Application.CalculateFull
        Application.CalculateUntilAsyncQueriesDone
        
        links = wb.LinkSources(xlExcelLinks)
        
        If Not IsEmpty(links) Then
            For Each link In links
                wb.BreakLink Name:=link, Type:=xlLinkTypeExcelLinks
            Next link
        End If
        
        On Error Resume Next
        wb.Sheets("Transaction Detail").Protect Password:="password", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
        On Error GoTo 0
        
        baseName = Replace(Left(fName, Len(fName) - 5), "Master", "")
        wb.SaveAs Filename:=saveFolder & baseName & suffix & ".xlsx"
        wb.Close SaveChanges:=False
        Set wb = Nothing
        
        fName = Dir
    Loop
        
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
        
    MsgBox "All files updated, links broken, and saved."
    
End Sub

The issue is isolated to the macro because when I use the Refresh All but in the ribbon it does refresh.

I've tried disabling all refreshes, and making a loop so that the macro doesn't skip over the refresh. Nothing is working.

Maybe before calling wb.RefreshAll you can make sure all queries are set to not run in the background? — Tim Williams
– Tim Williams, Commented Sep 3 at 0:55
How many queries are there, and what are they connecting to? — Tim Williams
– Tim Williams, Commented Sep 3 at 1:07

Storax · Accepted Answer · 2025-09-03 10:04:57Z

2

I'd suggest to remove wb.RefreshAll and to use a loop similar to the following in in order to refresh PowerQuery queries

    Dim wb As Workbook
    Dim qt As WorkbookConnection
    
    Set wb = ThisWorkbook
            For Each qt In wb.Connections
                Debug.Print qt.Name
                qt.OLEDBConnection.BackgroundQuery = False
                qt.Refresh
                qt.OLEDBConnection.BackgroundQuery = True
            Next qt

answered Sep 3 at 10:04

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Justin Parouty Sep 3 at 14:31

This worked. Thank you!

JSmart523 Oct 10 at 18:38

This refreshes the queries in serial. It would be better to refresh them without changing .BackgroundQuery and, in that same loop, add the queries' OLEDBConnection to a collection, followed by a do loop until the collection is empty. Within the loop, you sleep a second or two, then remove collection elements where .Refreshing is false. Once there's no more refreshing being done, the do loop ends.

Storax Oct 10 at 18:58

Right, it is serial. Why would it be better not to change .BackgroundQuery ? Please elaborate! Otherwise your argument lacks justification — it claims it’s “better” not to change .BackgroundQuery, but doesn’t explain why. In fact, toggling .BackgroundQuery to False ensures a controlled, synchronous refresh, while leaving it True risks concurrency issues if queries depend on each other. Without a clear reason (e.g., performance gain or async requirement), there’s no inherent advantage in avoiding the property change.

Collectives™ on Stack Overflow

How do I force refresh a PowerQuery with VBA?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related