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

2025年12月31日

Introduction

When importing data using Power Query in Excel, do you ever feel that clicking the [Refresh All] button in the “Data” tab every time is a hassle?

In this article, I will show you how to improve work efficiency by using VBA to create a macro that executes “Refresh All” with a single button click.

Goal

Eliminate the tedious process of clicking “Data” tab -> “Refresh All” every time.
Execute this operation with a single button press using VBA.
Target all connections in the Workbook, including Power Query.

VBA Code Used

Below is the VBA code to refresh all connections.

Sub RefreshAllConnections()
    ' Refresh all connections
    Dim connection As WorkbookConnection
    For Each connection In ThisWorkbook.Connections
        connection.Refresh
    Next connection
End Sub

Code Explanation

ThisWorkbook.Connections: Targets all data connections (Power Query and external data imports) contained in the current workbook.
connection.Refresh: 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:

Open the “Developer” tab from the Excel ribbon.
Select “Insert” -> “Form Controls” -> “Button”.
Draw the button on the worksheet.
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 updates Power Query queries as well. However, depending on some Web connections or external data settings, authentication errors may occur. In such cases, please review the connection settings on the Power Query side.