How to Refresh Power Query using VBA

Avoid manually running your query by hand every time and automate it using a few easy macros.

Contents:

1. Macros

1.1. Refresh a Specific Power Query

1.2. Refresh All Power Queries

Refresh Specific Query

Macro

Sub refresh_specific_query()

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Cleaned_Sales_Table").QueryTable.refresh BackgroundQuery:=False

End Sub

To refresh a specific Power Query table, you can use this macro. Replace "Sheet1" with the sheet that your query's output table is on, and replace "Cleaned_Sales_Table" with your output table's name.

Example: Refreshing a Specific Query

In this example, we have a table named "Sales_Table" and have a PowerQuery process that takes the Sales_Table:

And outputs the finished result in a table named Clean_Sales_Table to "Sheet1":

We can point the macro to a specific query table using VBA and have it refresh. In this case we are looking at the active workbook, on Sheet1, and the table we want to refresh is the "Cleaned_Sales_Table".

Then, using 'QueryTable.refresh', the Power Query that generated that table, will be refreshed.

'BackgroundQuery:=False' will disable the background refresh of any query. This allows each query to fully refresh before moving on to another query. This way, if you have multiple queries chained together, they won't fail by trying to all update at once.

This is also important if you have PivotTables connected to the query, as you would want to refresh the query table with new data before refreshing the PivotTable. Disabling the background refresh of any connections makes this easy.

Refresh All Power Queries in a Workbook

Macros

To all Power Query queries in a workbook you can use this macro:

Sub refresh_workbook()

    ActiveWorkbook.RefreshAll

End Sub

Note: This will refresh your entire workbook (your PivotTables, external connections, and PivotCaches).

If you only want to refresh Power Query queries, we can use a separate, slightly more complex macro:

Sub refresh_all_queries()

    For Each Worksheet In ActiveWorkbook.Worksheets
        For Each ListObject In Worksheet.ListObjects
            If ListObject.SourceType = Excel.XlListObjectSourceType.xlSrcQuery Then
                With ListObject.QueryTable
                    .BackgroundQuery = False
                    .refresh
                End With
            End If
            
        Next ListObject
        
    Next Worksheet
    
End Sub

This macro will look at each worksheet in the active workbook, and refresh every query table found in each worksheet.

This won't refresh your PivotTables so it is a safer bet if you have a file with other refresh-sensitive items.

Example: Refreshing All Queries in a Workbook

In this example, we have two tables and two queries that need to be refreshed. Those tables are Cleaned_Sales_Table and Cleaned_Customer_Table.

By using either macro above, we can refresh both of these queries. The macro's will go through each sheet, and where they find a query table like Cleaned_Sales_Table and Cleaned_Customer_Table, they will refresh and rerun them.