[Power Query x VBA] How to Create a “Refresh All” Button

When importing data using Power Query in Excel, clicking the [Refresh All] button in the “Data” tab every time can be annoying. In this article, I created a macro using VBA to execute [Refresh All] with a single button click to improve efficiency.

What I Want to Achieve

• I want to avoid manually clicking “Data” tab -> “Refresh All” in Excel every time.
• I want to execute this operation with a single button using VBA.
• I want to target all connections in the Workbook, including Power Query.

VBA Code Used

Below is the VBA code to update all connections.

Sub RefreshAllConnections()
    ' すべての接続を更新
    Dim connection As WorkbookConnection
    For Each connection In ThisWorkbook.Connections
        connection.Refresh
    Next connection
End Sub

Code Explanation

• ThisWorkbook.Connections This targets all data connections (Power Query, external data imports, etc.) included in the current workbook.
• connection.Refresh This refreshes each connection one by one. This automates the manual action of clicking [Refresh All].

How to Assign This Macro to a Button

You can use this macro by assigning it to a Form Control button placed anywhere on your Excel sheet.

Steps:

1. Open the “Developer” tab from the Excel ribbon.
2. Select “Insert” -> “Form Controls” -> “Button”.
3. Place the button on the worksheet.
4. When the “Assign Macro” screen appears, select RefreshAllConnections.

Now, simply clicking the button will refresh all connections, including Power Query.

Note: Compatibility with Power Query

This macro also targets and refreshes Power Query queries. However, depending on some Web connections or external data settings, authentication errors may occur. In that case, please review the connection settings on the Power Query side.

Summary

I was able to replace the manual effort of “Data -> Refresh All” with a single button using VBA.

• Eliminates the need for manual operation every time.
• Effective in preventing you from forgetting to update.
• Allows batch updating even for files with multiple connections.

If you want to improve your work efficiency even a little, please try incorporating this.