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.RefreshAllwork 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
- Free Excel Mileage Log Templates: 7 Real Options
- Excel Lowercase: Convert Text to Lowercase
- Excel Sales Tracking Template by Region
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Print GMT Time Using Excel VBATo print the current GMT time with Excel VBA on Windows, call the Windows GetSystemTime API and send…
- Excel VBA: Check Whether a Worksheet ExistsTo check whether a worksheet exists in Excel VBA, loop through the target workbook’s Worksheets collection and compare…
- Excel VBA Macro: How to Create, Run, and Save One SafelyTo create a VBA macro in Excel for Microsoft 365 on Windows, turn on the Developer tab, choose…
- Excel VBA Compile Error: Syntax Error Causes and FixesAn Excel VBA “Compile error: Syntax error” message means VBA cannot parse part of your code as valid…
- VBA Remove Item From Dictionary: Methods and ExamplesTo remove one entry from a VBA dictionary, call dict.Remove key, where key is the entry’s key—not its…
- VBA Len String Variable: Count Characters CorrectlyIn VBA, use Len(stringVariable) to get the number of characters in a string; the result is a Long.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.