To refresh all supported external data connections and PivotTable reports in an Excel workbook with VBA, call ThisWorkbook.RefreshAll (or the workbook you intend to refresh). In Microsoft 365 for Windows, the same command is available from Data > Refresh All; if VBA must wait for pending OLE DB or OLAP queries, it can also call Application.CalculateUntilAsyncQueriesDone, with the limitation that this is not a universal completion signal for every kind of data source.
What Refresh All does in Excel
Excel’s Refresh All command asks the workbook to update its external data ranges and PivotTable reports. Depending on the workbook, that can include Power Query results, workbook connections to databases or files, web queries, and PivotTables based on external or cached data. The exact set depends on what the workbook contains and how each connection is configured.
In VBA, the key method is Workbook.RefreshAll. Calling it on ThisWorkbook targets the workbook that contains the macro. Calling it on ActiveWorkbook targets whichever workbook is active at that moment, which can be a different file. Prefer the explicit workbook reference when a macro may open, activate, or switch between files.
Refresh All does not mean “recalculate every formula” in the broadest sense. Excel’s calculation engine recalculates formulas according to its calculation settings and dependencies. Nor does the command necessarily update every value that happens to appear in a workbook: static pasted data, external links outside the connection system, and add-in-specific data may need their own update method.
How a refresh runs
A refresh asks the relevant connection or report to retrieve or rebuild data. For a database connection, Excel may send a query to a server; for a Power Query query, it may run the query steps and load the resulting table or data model; for a PivotTable, it may refresh the underlying cache or source. The work can take seconds or much longer depending on network access, source size, credentials, and server response.
One important detail is background refresh. Some connections can run asynchronously, allowing VBA to continue while Excel is still retrieving data. As a result, a macro that calls RefreshAll and immediately reads a refreshed table may read the old values. This is one of the most common reasons a macro seems to refresh successfully but produces a stale export or report.
Application.CalculateUntilAsyncQueriesDone waits for pending asynchronous OLE DB and OLAP queries. It can be useful after Refresh All, but its documented scope is specific: it does not promise to track every add-in, custom query, or other asynchronous operation. For a workflow that depends on a particular connection, use that connection’s supported status or completion event where available, and test the behavior with the actual data source.
A basic VBA macro for Refresh All
This example refreshes the workbook containing the code and then waits for pending OLE DB and OLAP queries. Save a copy of the workbook before running any macro: VBA actions cannot be undone with Ctrl+Z. Store the file in a macro-enabled format such as .xlsm if it needs to retain VBA.
Sub RefreshWorkbookData()
Dim wb As Workbook
Set wb = ThisWorkbook
' Refresh external data and PivotTables in this workbook.
wb.RefreshAll
' Wait for pending OLE DB and OLAP queries to finish.
Application.CalculateUntilAsyncQueriesDone
End Sub
To refresh a different workbook, assign the relevant workbook object to wb instead of relying on whichever file happens to be active. For example, if the macro has already opened a workbook and stored its reference in a variable named reportBook, use that reference. Avoid writing ActiveWorkbook.RefreshAll in automation unless you have deliberately verified which workbook is active.
The sample does not display a success message because the wait method’s scope is limited, and a message could imply that every possible refresh has completed. A production macro should also consider what to do if a source is unavailable, a credential prompt appears, or a query returns an error. Error handling should report the problem clearly rather than silently treating the refresh as successful.
Running the macro and refreshing manually
In Excel for Microsoft 365 on Windows, select Data > Refresh All to refresh manually. The Windows keyboard shortcut is Ctrl+Alt+F5. To run a saved macro, use Developer > Macros, select the procedure, and choose Run; the Developer tab can be enabled through File > Options > Customize Ribbon if it is hidden. Macros must be permitted by the workbook’s security settings.
Excel for Mac also supports VBA and workbook refresh commands, though connection availability and behavior can differ by source and Excel release. Use the Data tab’s refresh command where available, and check the specific connector’s support on Mac. The Windows shortcut should not be assumed to work on Mac; keyboard shortcuts can vary by version, keyboard, and macOS settings. Excel for the web does not run VBA macros, so a workbook opened there cannot execute this procedure.
Before using a macro on a shared or business workbook, confirm that you are refreshing the intended file and that the source is safe to contact. A refresh can retrieve new records, change table contents, update PivotTable results, and use network or server resources. A refresh can also take long enough that users should not assume the workbook is ready immediately after clicking the command.
Choose the right refresh scope
Refresh All is convenient when the workbook’s connected data should be updated together. It may be too broad when only one query or PivotTable needs attention. Excel exposes more targeted options through the relevant table, query, or PivotTable controls, and VBA can call refresh methods on individual objects when the workbook structure and object types are known.
- Refresh the whole workbook: use
ThisWorkbook.RefreshAllwhen all workbook connections and PivotTable reports are meant to update. - Refresh one PivotTable: select the PivotTable and use PivotTable Analyze > Refresh, or use its VBA object when targeting it programmatically.
- Refresh one query or table: use the query’s refresh control or its associated connection/table method, after confirming the object name and connection type.
- Refresh linked workbooks: handle external workbook links separately if updating those links is part of the task; Refresh All is not a substitute for reviewing every linked-file value.
Targeting fewer objects can make a process quicker and reduce unnecessary requests to a database or service. It also introduces more maintenance: a macro aimed at one named query can break if someone renames or deletes that query. For a reusable workbook, document which data sources are expected to refresh and why.
Background refresh and timing choices
Some query and connection settings allow background refresh. When enabled, Excel can return control while data is still loading. This is helpful for interactive work, but potentially risky when the next VBA step depends on completed results. For example, a macro that refreshes a sales query and immediately copies its table into an email attachment might copy the previous day’s rows if the query is still running.
Where the connection type exposes a background-refresh setting, disabling background refresh can make a refresh run synchronously, but not every connection type offers the same controls. Do not assume that one setting applies to all workbook connections. A better design is to check the behavior of each required source and wait for a suitable completion condition before using its output.
Waiting for a fixed number of seconds with a timer is generally fragile. A refresh may finish sooner on one day and take longer on another, so an arbitrary pause can either waste time or still allow stale data through. If a refresh is part of a scheduled process, also consider timeouts, logging, and a clear failure path rather than leaving a macro indefinitely waiting without explanation.
Common problems and practical checks
If the refresh appears to do nothing, the problem may be outside the VBA line itself. Check whether the workbook actually contains a refreshable connection, whether the query is enabled, whether the source is reachable, and whether Excel is waiting for credentials or a privacy-level decision. A workbook can also contain results loaded to a worksheet or data model while the visible table has not changed because the source returned the same records.
- Old values are used after the macro: the connection may be refreshing in the background. Confirm its completion before downstream steps run.
- A prompt interrupts automation: credentials, permissions, privacy settings, or a confirmation dialog can require user action. Resolve the source configuration rather than assuming VBA can bypass it.
- Only some data changes: connections may have different refresh settings or may fail independently. Check each query’s status and the workbook’s connection list.
- A PivotTable still looks unchanged: verify its source and cache, then check whether the refreshed source actually contains changed values. Formatting or filters can also hide new records.
- The macro does not run: verify the file type, macro security, procedure location, and that Excel for the web is not being used to run VBA.
Refresh behavior can also be affected by network permissions, expired passwords, unavailable files, firewall rules, or source-system limits. If the workbook is shared, coordinate with its owner before changing connection properties: a setting that makes one user’s refresh faster may increase load on a shared database or change the experience for other users.
Refresh All versus recalculation and refresh on open
Refreshing data and recalculating formulas are related but distinct tasks. Refresh All asks connections and PivotTables to update their data. Formula recalculation evaluates formulas using the values currently available. A workbook can therefore need both: a query refresh to bring in current transactions, followed by calculation of formulas that summarize those transactions. Excel normally recalculates dependent formulas as data changes, subject to calculation mode and workbook settings.
Refresh-on-open settings are another separate choice. They can make a query update when the workbook opens, but they do not mean that every refresh has finished before another macro reads the results. If both refresh-on-open and a VBA Refresh All call are enabled, the workbook may initiate redundant work. Review the connection properties and macro sequence so the same source is not unintentionally refreshed twice.
Refresh All is also different from updating links to other workbooks. Workbook links can be reviewed and managed through Excel’s link controls, and their update behavior depends on link settings and prompts. If a report depends on both query connections and linked files, treat them as separate dependencies and make the sequence explicit.
Frequently Asked Questions
What is the VBA code for Refresh All in Excel?
Use ThisWorkbook.RefreshAll to refresh the workbook that contains the macro. Use a specific Workbook variable when the target is another open workbook. Add a suitable wait or completion check if later code must use the refreshed results.
Does Workbook.RefreshAll wait until every query finishes?
Not necessarily. Connections configured for background refresh may continue after the method returns. Application.CalculateUntilAsyncQueriesDone waits for pending OLE DB and OLAP queries, but it is not a guarantee for every connector, add-in, or custom asynchronous process.
How do I refresh all data when I open a workbook?
You can configure supported connections to refresh when the workbook opens, or call ThisWorkbook.RefreshAll from a workbook-open event procedure. Check the connection’s own options and test whether the refresh is complete before any event code reads its output.
Why does my macro refresh but still copy old data?
The refresh may be running in the background, or the macro may be reading a different table or workbook than the one refreshed. Confirm the connection’s completion, check the target workbook reference, and verify that the query actually returned changed data.
Can Excel for the web run a Refresh All VBA macro?
No. Excel for the web does not execute VBA. A workbook may offer web-based data refresh features depending on its source and configuration, but the VBA procedure must run in a desktop version of Excel that supports the macro and connection.
Can Refresh All update formulas and external workbook links too?
It updates connected data and PivotTable reports, while formulas recalculate according to Excel’s calculation settings. External workbook links are a separate mechanism and may require their own update handling; check each dependency instead of assuming one command covers all workbook content.
Related
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary Add Item: Syntax and ExamplesIn Excel VBA, add an item to a Scripting.Dictionary with dict.Add key, item; create the dictionary first, and…
- Microsoft Excel VBA For Loops: Syntax and ExamplesIn Microsoft Excel VBA, a For...Next loop repeats instructions a set number of times, while For Each...Next repeats…
- Excel VBA Get Array Dimensions: Bounds, Rank, and SizeIn Excel VBA, use LBound(array, dimension) and UBound(array, dimension) to get an array’s lower and upper bounds; its…
- Excel VBA Frame With Scroll Bar: Setup and CodeTo make a scrollable area in Excel VBA, add a Frame to a UserForm, set its ScrollBars property…
- Excel VBA Error Handler: How to Catch and Fix ErrorsAn Excel VBA error handler is code that tells a macro what to do when a runtime error…
- Excel to PowerPoint VBA: How to Automate SlidesExcel VBA can automate PowerPoint on Windows: select worksheet ranges, copy them, create slides, and paste the content…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.