Excel VBA Refresh All: How to Update Workbook Data

To run Excel’s Refresh All command from VBA, use ThisWorkbook.RefreshAll in a macro. It asks Excel to refresh the workbook’s external data connections and PivotTable reports; some refreshes may continue in the background after the macro moves on.

What Refresh All actually updates

RefreshAll is a method of Excel’s Workbook object. In a macro, ThisWorkbook refers to the workbook that contains the running code, so ThisWorkbook.RefreshAll targets that workbook. It is usually the clearest choice when the macro is stored in the workbook whose data needs updating.

The command can refresh external data ranges, workbook connections, and PivotTable reports. The exact results depend on how each source and report is configured. For example, a workbook might contain a Power Query connection, a database connection, and several PivotTables. Refresh All asks Excel to update these items, but source availability, credentials, connection properties, and refresh order can affect what completes.

Refresh All is not the same as recalculating every formula. Excel calculation updates formula results based on the values currently in the workbook; refreshing retrieves or reloads data from a source. A refresh can lead Excel to recalculate dependent formulas, but if your goal is only to recalculate formulas, use Excel’s calculation commands or VBA calculation methods instead.

Run Refresh All from a VBA macro

To create a basic macro in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this procedure. Save a copy of the workbook before running a macro: VBA changes and refresh results cannot be undone with Ctrl+Z.

Public Sub RefreshWorkbookData()
    Dim wb As Workbook

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook

    ' Ask Excel to refresh its data connections and PivotTables.
    wb.RefreshAll
End Sub

Save the file in a macro-enabled format such as .xlsm if it contains VBA. Run the macro from the Visual Basic Editor with the cursor inside the procedure and press F5, or return to Excel, press Alt+F8, select RefreshWorkbookData, and choose Run. You can also assign the macro to a button or shape.

On a Mac, the Visual Basic Editor can be opened from the Developer tab or with the applicable Option+F11 keyboard shortcut; some keyboards require the Fn key as well. The ribbon and macro dialog can differ slightly from Windows. Excel for the web does not run VBA macros, so open the workbook in the desktop Excel app to use this code.

Understand background refresh and completion

A macro calling RefreshAll may finish before every data source has finished loading. Some connections allow background refresh, which lets Excel return control while a request continues. That means a later line in the same macro could run while data is still arriving. A message box, export, or calculation placed immediately after the refresh call may therefore use old or incomplete results.

For pending OLE DB and OLAP queries, Excel provides Application.CalculateUntilAsyncQueriesDone. You can call it after Refresh All when those query types are involved:

Public Sub RefreshAndWaitForAsyncQueries()
    Dim wb As Workbook

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook
    wb.RefreshAll

    ' Wait for pending OLE DB and OLAP queries to finish.
    Application.CalculateUntilAsyncQueriesDone
End Sub

This wait method is not a universal guarantee that every kind of connection, add-in, or external process has completed. Confirm the behavior of the particular data sources in your workbook. If subsequent code depends on refreshed results, test the full sequence with representative data and check the connection’s refresh settings rather than assuming the call has finished everything.

Refresh one connection, query, or PivotTable

Refresh All is convenient, but it can take longer than necessary if you only need one item. Refreshing a specific object can make a process more targeted, provided you know its name and understand its dependencies. For example, a PivotTable may rely on a connection or cache that should be refreshed first.

One workbook connection: use the connection’s Refresh method, such as ThisWorkbook.Connections("Connection Name").Refresh. Replace the sample name with the actual connection name in the workbook.
One PivotTable cache: use the relevant cache’s Refresh method. A cache can feed more than one PivotTable, so refreshing it may affect multiple reports.
One query table: a QueryTable can be refreshed directly, with its refresh behavior determined by its settings. A synchronous refresh can wait for that query, but its availability and behavior depend on the object and source.

To identify connections, open Data > Queries & Connections and inspect the workbook’s listed queries and connections. For PivotTables, click inside the report and use the PivotTable Analyze tab to inspect its options. Avoid guessing object names: a misspelled name can produce a VBA error, and refreshing only one part of a dependency chain can leave the workbook inconsistent.

Choose when the macro should run

You can run the macro manually, assign it to a button, or call it from another procedure. Manual refresh is often the simplest option for a workbook where a person should decide when to contact a data source. A button can make the action obvious to other users without requiring them to open the VBA Editor.

Some workbooks refresh connections when opened. That behavior is configured through connection properties and may vary by connection type. An automatic refresh on open is not identical to adding a refresh call in a workbook event procedure: event code runs as part of the workbook’s VBA events and can be affected by macro security or other event procedures. If you add automatic code, consider whether opening the workbook should always trigger network access, a login prompt, or a lengthy data load.

For a scheduled or unattended process, do not assume that a desktop workbook will refresh reliably just because a macro works when run interactively. The computer must have access to the source, required credentials, and a suitable Excel environment. Cloud storage, protected views, prompts, and user-specific authentication can all change the result.

Common Refresh All problems

If the macro runs but the displayed data does not change, investigate the source and connection rather than repeatedly running the same command. Open Data > Queries & Connections and check for errors or a connection that is still refreshing. Then check whether the source itself has newer records and whether the query’s filters or parameters exclude them.

Credentials or access denied: sign in to the source or update the stored permissions. A macro cannot bypass the source’s authentication requirements.
Source unavailable: verify the network, server, file path, or URL. A connection that points to a file moved by another user may fail even though the VBA code is correct.
Refresh appears incomplete: check whether background refresh is enabled and whether the macro continues before the query completes. Use an appropriate wait strategy for the connection type.
PivotTable still looks old: confirm that its source or cache was refreshed and that filters, slicers, and report settings are not hiding the new records.
Macro does not run: confirm that the workbook is macro-enabled and that Excel permits the macro under the organization’s security policy. Do not enable macros in an untrusted file just to test it.

A refresh can also fail because a source has changed its structure, such as renaming or removing a column used by a query. In that case, the source may be reachable while the query still reports an error. Read the error details in Excel’s query or connection interface and repair the source mapping or query steps as appropriate.

Refresh All versus the Excel command

The Data > Refresh All command in Excel and the VBA Workbook.RefreshAll method serve the same general purpose: ask Excel to refresh workbook data. Use the ribbon command for a one-time interactive update. Use VBA when you want a repeatable button, a controlled sequence, or a refresh as part of a larger workflow.

Neither approach automatically fixes broken credentials, unavailable sources, or query errors. Neither should be confused with recalculating formulas. Excel’s calculation mode can also affect when formulas update, so a workbook set to manual calculation may need an explicit calculation step after data refresh if formula results must be updated immediately.

Security and platform considerations

Refreshing data can send requests to external services or databases, and a macro can perform actions beyond refreshing. Only run VBA from workbooks you trust, and follow your organization’s macro and data-access policies. Before distributing a workbook, consider whether recipients have permission to access the same sources and whether connection details expose information that should remain private.

VBA is supported in desktop Excel, including Excel for Microsoft 365 on Windows and Mac, although some data connectors and interface details differ by platform. Excel for the web can open many workbooks but does not execute VBA. If users need a browser-based automation approach, Office Scripts may be an alternative in supported Microsoft 365 environments, but it is a different technology and its APIs and availability are not interchangeable with VBA.

Frequently Asked Questions

What is the VBA code for Refresh All?

Use ThisWorkbook.RefreshAll to ask Excel to refresh the workbook containing the macro. Put it in a standard VBA module, save the file as macro-enabled, and run the procedure from desktop Excel.

Does RefreshAll recalculate formulas?

Refresh All primarily requests updates to connections, external data ranges, and PivotTable reports. Formula calculation is a separate Excel process, even though refreshed values may cause dependent formulas to recalculate.

Why does my macro finish before the data refresh does?

Some connections refresh in the background, so VBA can continue while Excel is still retrieving data. The available way to wait depends on the connection type; CalculateUntilAsyncQueriesDone covers pending OLE DB and OLAP queries, not every possible refresh process.

Can I use RefreshAll in Excel for the web?

No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel application to run a VBA refresh macro.

Can I refresh only one PivotTable?

Yes. You can refresh the relevant PivotTable cache instead of refreshing the entire workbook. Check whether other PivotTables share that cache and whether the report depends on a connection that must be refreshed first.

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.