Yes. In Excel for Microsoft 365 on Windows, a VBA macro can refresh the workbook’s refreshable external data ranges and PivotTable reports by calling ThisWorkbook.RefreshAll. The refresh may continue in the background after the macro starts it, so a successful macro run does not always mean every query has finished updating.
What Refresh All Does
Excel’s Refresh All command asks the workbook to update its supported external data and PivotTable content. That can include Power Query queries, workbook connections, linked data ranges, and PivotTables, depending on how each item is configured and whether its source is available. The equivalent ribbon command is Data > Refresh All.
The macro uses the workbook-level RefreshAll method, so it is useful when you want to start the same broad refresh from a button, another macro, or a workbook-opening procedure. It is not a command to refresh every possible object in every situation: unsupported sources, disconnected services, disabled queries, or items excluded by their settings may not update.
Refresh All also does not guarantee that every formula has recalculated in the way you expect, nor does it fix missing credentials or source errors. It requests data refreshes; Excel and the relevant connector determine how each source responds.
A Basic VBA Macro
This short macro refreshes the workbook that contains the code. It is a sensible starting point when the macro is stored in the workbook whose data you want to update.
Sub RefreshAllDataConnections()
Dim workbookToRefresh As Workbook
Set workbookToRefresh = ThisWorkbook
' Start refreshes for refreshable data ranges and PivotTables.
workbookToRefresh.RefreshAll
End Sub
ThisWorkbook means the workbook in which the VBA code is stored. That distinction matters: if you put the macro in your Personal Macro Workbook, ThisWorkbook refers to that personal file, not the workbook currently on screen. In that case, you would need to target the intended workbook explicitly, for example by using ActiveWorkbook after checking that it is the correct file.
The call starts refresh activity; it does not contain a separate line for each connection. That is what makes it convenient for a workbook with several queries or PivotTables. It also means you should inspect the workbook’s connection settings if one item consistently fails to refresh.
How to Add and Run the Macro
- Save a working copy of the workbook before adding or running VBA. Macros cannot be undone with Ctrl+Z, and a refresh can replace data in query output tables.
- Save the file as an Excel Macro-Enabled Workbook (
.xlsm) using File > Save As. A regular.xlsxfile cannot store VBA code. - Open the VBA editor from Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel’s ribbon settings.
- In the editor, choose Insert > Module, paste the macro into the module, and save the workbook.
- Return to Excel and run it from Developer > Macros, select
RefreshAllDataConnections, and choose Run.
Excel may show a security warning when you open a macro-enabled file. Only enable macros for files you trust; do not lower macro security globally just to run a refresh. Excel for Mac also supports VBA macros, though available data connectors and refresh behavior can differ from Windows. Excel for the web does not run VBA macros, so use the desktop app for this approach.
Background Refresh and Waiting for Completion
A frequent surprise is that VBA continues to the next line while a connection is still refreshing. Some queries run synchronously, while others use background refresh. With background refresh enabled, Excel can return control before the new data has arrived. A macro that immediately copies query results, exports a report, or sends an email may therefore act on old data.
For an individual connection, check its refresh properties in Excel. Depending on the connection type, the setting may be available through Data > Queries & Connections, the connection’s properties, or the properties of its output table. If the connection offers a background-refresh option, turning it off can make that refresh wait before proceeding, but the option is not available or applicable to every connector.
Excel VBA also provides Application.CalculateUntilAsyncQueriesDone to wait for pending asynchronous queries of certain types, including OLE DB and OLAP queries. It can help in workflows that use those query types, but it is not a universal completion detector for every connector or every later calculation. Do not assume it proves that all Power Query, web, or other source activity has finished. For a process that must run only after a particular query completes, use a completion event or a connector-specific method where available, and verify the behavior for that source.
A fixed pause, such as waiting ten seconds, is not a reliable substitute. A fast refresh wastes time, while a slow network or large query may need longer. If the workbook must take a follow-up action, design that action around the relevant query’s completion rather than guessing a duration.
Which Connections Are Included?
Refresh All works at workbook scope, but refresh eligibility and settings still matter. A workbook might contain Power Query queries, legacy connections, external tables, PivotTables, and connections used only by the Data Model. Their visible results and refresh controls are not identical, and a query can load to a worksheet, to the Data Model, or only as a connection.
- Power Query: A query that is configured to refresh and has an accessible source can generally be included in a workbook refresh. Credentials, privacy settings, source paths, and connector availability can prevent success.
- PivotTables: Refresh All can refresh PivotTable reports and their underlying sources, but the result depends on how the PivotTable is connected and whether its cache or source can be refreshed.
- External data ranges: Output tables tied to supported connections can refresh, subject to their connection properties and source requirements.
- Manual or unavailable sources: A query may be disabled, set up for manual refresh, or unable to authenticate. The macro cannot override those conditions simply by calling Refresh All.
For a particular query, use Data > Queries & Connections to review its status and properties. The pane is also useful for seeing whether a refresh is still running or whether Excel reports an error.
Refreshing on Open or from a Button
If the goal is to update data whenever a workbook opens, first check whether the individual connection already has a Refresh data when opening the file option. Using the built-in setting is often simpler than adding VBA. It also makes the behavior visible in the connection’s properties.
If you need a macro to run on opening, VBA supports the workbook-open event. Put this event procedure in the workbook’s ThisWorkbook code module, not in a regular module:
Private Sub Workbook_Open()
' Start refreshes when this workbook opens.
ThisWorkbook.RefreshAll
End Sub
Opening a workbook can then trigger source access, sign-in prompts, or refresh errors, so this is not always appropriate for files shared with others. Consider whether users expect an automatic update and whether the workbook opens in a trusted desktop environment. An alternative is a worksheet button assigned to a standard-module refresh macro, giving users a clear choice about when to retrieve data.
Common Problems and Practical Checks
If the macro runs but a result looks unchanged, start with the query or connection rather than rewriting the VBA. Run Data > Refresh All manually and inspect the Queries & Connections pane. This helps distinguish a code problem from a source, permissions, or configuration problem.
- Credentials or permissions: Sign in to the source or confirm that the account running Excel has access. A macro does not bypass authentication.
- Changed file path or server: Check whether the source file moved, a network drive is unavailable, or a server address changed.
- Refresh still running: Look for a refresh indicator or query status before using the output in a later step.
- Background refresh: Review whether the connection returns control before completion and whether its settings allow background operation.
- Macro stored in the wrong workbook: Confirm that
ThisWorkbookis the file containing the data connections you intend to refresh. - Excel version or platform: Connector support and interface details can differ on Mac and in Excel for the web. VBA itself must run in desktop Excel.
Refreshes may also take longer when the source is large, the network is slow, or several queries depend on one another. Avoid starting multiple refreshes repeatedly while one is still active; that can make it harder to tell which request produced the visible result.
When a Different Method Is Better
For a one-time update, the ribbon command Data > Refresh All is usually the simplest option and requires no macro security setting. For a workbook used by people who should choose when data is retrieved, a button assigned to the macro can be clearer than refreshing automatically on open.
For a scheduled or unattended process, consider whether desktop Excel is the right environment. VBA depends on the desktop application and may encounter prompts, unavailable credentials, or dialogs that prevent a hands-off run. Depending on the organization’s tools and data sources, a managed data-refresh service or an automation platform may be a better fit. Check support for the specific connector and authentication method before relying on any automated workflow.
Frequently Asked Questions
Does RefreshAll refresh Power Query in Excel?
It can refresh Power Query queries that are configured for refresh and whose sources and credentials are available. If a query does not update, inspect its settings and status under Data > Queries & Connections.
Does the macro wait until every connection finishes?
No. ThisWorkbook.RefreshAll starts the refreshes, and background queries can continue after the call returns. Waiting behavior depends on the connection type and settings; there is no single wait method that safely guarantees completion for every possible connector.
Will this macro refresh PivotTables too?
The workbook-level Refresh All operation includes refreshable PivotTable reports as well as external data ranges. Whether a particular PivotTable updates depends on its source and configuration.
Can I run the macro in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in the desktop Excel app to run this code; some data-refresh features may also vary by platform.
Can I make Excel refresh connections when the file opens?
Yes. You can enable a connection’s refresh-on-open setting where available, or use a Workbook_Open event procedure in VBA. Automatic refresh can prompt for credentials or fail when a source is unavailable, so test it in the environment where the workbook will be used.
Related
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Create a UserForm in Excel VBA: A Step-by-Step GuideTo create a UserForm in Excel VBA, open the Visual Basic Editor, choose Insert > UserForm, add controls…
- Excel VBA Find Partial Text: Methods, Code, and TipsTo find cells containing part of a text string in Excel VBA, use the range’s Find method with…
- Excel Macro Recorder Key: Start, Stop and Assign ShortcutsIn Excel for Microsoft 365 on Windows, there is no default keyboard shortcut that starts the macro recorder.…
- Excel VBA Copy and Paste a Column: Examples and TipsTo copy a column with Excel VBA, use sourceWs.Columns("B").Copy Destination:=targetWs.Columns("D"), replacing the worksheet variables and column letters with…
- Excel Macro Recorder: Enterprise Safety, Uses, and LimitsExcel’s built-in Macro Recorder is available in desktop Excel for Microsoft 365, but it is not a separate…
- Excel VBA Shortcut Key: Open the Editor and Run MacrosIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic for Applications (VBA) editor.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.