Refresh queries

Queries can be refreshed in a number of ways.

• Manually
• On opening the file
• Via VBA
• Via Office Scripting

Which option is best?

If you are doing it yourself, manual refreshes.
If somebody else is using the workbook and the datasets are small, set the file to refresh on open.

The Office script is OK, but needs an Internet connection and the code is not saved in the file.
The VBA option is solid, but cannot be used in Excel for the web.

Manual Refresh

There are a few options:

• Queries can be refreshed by selecting a cell in the query results, right clicking on the cell and selecting Refresh.
• If the Queries & Connections sidebar is open, right click on a query and select Refresh.
• To refresh all queries, select the Data menu and click on the Refresh All icon.

Refresh on file open

Select the Data menu and click the Queries & Connections icon.
The Queries & Connections sidebar will open.
Right click on a query and select Properties.
Tick Refresh data when opening the file and click the OK button.

Each time the file is opened, the query will be refreshed.

VBA

It is possible to refresh a query by adding a button and linking a VBA macro to it.

This is an example of the code to refresh queries.
Either one or all queries can be refreshed.

    Sub Refresh_one_and_all()

        Range("A10").ListObject.QueryTable.Refresh BackgroundQuery:=False
    'Refresh a single query. Option 1
        
        ActiveWorkbook.Connections("Query - Workflow").Refresh
    'Refresh a single query. Option 2
        
        ActiveWorkbook.RefreshAll
    'Refresh everything
        
    End Sub

Office Scripting

An Office script can be linked to the Workbook via the Automate menu.
Currently this can only refresh all data.

    function main(workbook: ExcelScript.Workbook) {
        // Refresh all data connections
        workbook.refreshAllDataConnections();
    }

Office Scripts are stored in your OneDrive under /Documents/Office Scripts/ folder as .osts files by default.

The script can be linked to the Workbook in Excel online, so that other users can run it.