Excel Script to Refresh Power Query? (VBA & More)

VBA & macros · Updated November 22, 2026

Yes, you can refresh Power Query with an Excel script. The most common method is a VBA macro using ThisWorkbook.RefreshAll, which refreshes all queries and pivot tables in the workbook. For a single query, use ThisWorkbook.Connections("Query name").Refresh. You can also use Office Scripts (for Excel on the web and desktop) with the workbook.refreshAll() method, or simply press Ctrl+Alt+F5 to refresh all queries manually.

Why Automate Power Query Refreshes?

Power Query (officially called Get & Transform) loads data from sources like CSV files, databases, or web APIs. Refreshing manually is fine for one-off tasks, but automation saves time when you have multiple queries, need to refresh on a schedule, or want to trigger a refresh when a file opens. Automation also reduces the risk of forgetting to refresh before sending a report to a colleague or client.

VBA Macro to Refresh All Queries

VBA (Visual Basic for Applications) is the built-in macro language for Excel on Windows and Mac. To refresh all Power Query queries and pivot tables, use this simple macro:

Sub RefreshAllQueries()
    ' Refreshes all connections, including Power Query queries
    ThisWorkbook.RefreshAll
    MsgBox "All queries refreshed."
End Sub

To run it, press Alt+F11 to open the VBA editor, insert a new module (Insert > Module), paste the code, and press F5 while the cursor is inside the sub. On a Mac, the shortcut is Fn+Option+F11.

If you only want to refresh one specific query (e.g., “SalesData”), use:

Sub RefreshOneQuery()
    ' Replace "SalesData" with your query name
    ThisWorkbook.Connections("SalesData").Refresh
End Sub

Note: The query name is the one shown in the Queries & Connections pane (Data > Queries & Connections), not necessarily the sheet name.

Office Scripts: The Modern Alternative

Office Scripts are the newer automation tool for Excel, available in Excel for Microsoft 365 on Windows, Mac, and the web. They use TypeScript and can be triggered manually or via Power Automate. To refresh all queries with an Office Script:

function main(workbook: ExcelScript.Workbook) {
    workbook.refreshAll();
    console.log("All queries refreshed.");
}

You can create this script from the Automate tab in Excel. Office Scripts are safer than VBA because they run in a sandboxed environment and cannot modify files outside Excel. However, they require a Microsoft 365 subscription and do not support all VBA features.

How to Choose Between VBA and Office Scripts

Your choice depends on your Excel version and needs:

  • VBA works in all desktop versions of Excel (2010 and later) and is ideal for complex logic, userforms, or interacting with other Office apps. It also works on Mac, though some features differ.
  • Office Scripts are only available in Excel for Microsoft 365 (desktop and web). They are easier to share and maintain, and they integrate with Power Automate for scheduled refreshes.
  • If you need to refresh on a schedule without opening Excel, use Power Automate’s “Excel Online (Business) – Refresh a workbook” action, which runs an Office Script.

Refreshing on File Open Automatically

To refresh all queries every time you open a workbook, you can add a small VBA macro to the Workbook_Open event. In the VBA editor, double-click ThisWorkbook in the Project Explorer and paste:

Private Sub Workbook_Open()
    ' Refresh all queries when the workbook opens
    ThisWorkbook.RefreshAll
End Sub

Alternatively, go to Data > Queries & Connections > Queries tab, right-click a query, select Properties, and check “Refresh data when opening the file.” This works without VBA but applies to each query individually.

Common Problems and Fixes

When automating refreshes, you may encounter these issues:

  • “Background refresh” errors: If a query fails, check the query steps for changes in source data (e.g., a renamed column). Use Data > Queries & Connections > right-click query > Edit to review.
  • Credentials: If your data source requires authentication, ensure the query uses the correct credentials. In the query properties, you can set “Always use” for credentials.
  • Performance: Refreshing all queries can be slow. To speed up, disable background refresh (Data > Queries & Connections > right-click > Properties > uncheck “Enable background refresh”) or refresh only the needed queries.
  • VBA macro not working on Mac: Some VBA functions like ThisWorkbook.RefreshAll work on Mac, but file paths and some UI elements differ. Test on your platform.

Comparison: VBA vs Office Scripts vs Manual

Here’s a quick comparison to help you decide:

  • Manual (Ctrl+Alt+F5): Fastest for one-off refreshes, no coding required. Works on all versions.
  • VBA: Full control, works offline, can be attached to buttons or events. Requires enabling macros, which may be blocked by security settings.
  • Office Scripts: Cloud-friendly, can be scheduled via Power Automate, works on web. Requires Microsoft 365 and an internet connection for some actions.

Frequently Asked Questions

Can I refresh Power Query without opening Excel?

Yes, you can use Power Automate to run an Office Script that refreshes the workbook, or use a scheduled task on Windows to open Excel and run a VBA macro. The latter requires keeping Excel open or using a command-line switch.

What is the shortcut to refresh all queries?

Press Ctrl+Alt+F5 to refresh all data connections. On a Mac, use Cmd+Option+F5 (if your keyboard has F5). You can also right-click a query in the Queries & Connections pane and choose Refresh.

Why is my VBA refresh macro not working?

Common reasons: macros are disabled (enable via File > Options > Trust Center), the query name is misspelled, or the data source is unreachable. Check the Immediate window in VBA (Ctrl+G) for error messages.

Can I refresh only a specific query with Office Scripts?

Yes, but there is no direct method. You can loop through connections and check the name, or use the workbook.getConnections() API and filter. However, refreshAll() is simpler and often sufficient.

Does refreshing Power Query affect pivot tables?

Yes, if the pivot table is based on a Power Query result (e.g., a worksheet table or data model), refreshing the query will update the pivot table as well. If not, you may need to refresh the pivot separately.

Is there a way to refresh Power Query from a button?

Yes, you can insert a shape or button (Insert > Shapes) and assign a VBA macro to it. Right-click the shape, choose Assign Macro, and select your refresh sub. This works on Windows; on Mac, the process is similar.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.