Excel Macro: Refresh All Data Connections on Workbook Open

Your workbook may have connections to external data sources such as web queries, MSQuery connections, PivotTable connections, and so on. In these cases, it may be helpful to refresh all data connections automatically when the workbook is opened. The following macro code allows you to refresh all data connections on Workbook open.

Refresh All Data Connections on Workbook Open

'------------------ ThisWorkbook ------------------
Private Sub Workbook_Open()
'Step 1: Use the RefreshAll method
    Workbooks(ThisWorkbook.Name).RefreshAll
End Sub

How This Macro Works

This macro is an easy one-liner that uses the RefreshAll method. This method refreshes all the connections in a given workbook or worksheet. In this case, we are pointing it to the entire workbook.

The thing to note in this macro is that we are using the ThisWorkbook object. This object is an easy and safe way for you to point to the current workbook.

ThisWorkbook or ActiveWorkbook

The difference between ThisWorkbook and ActiveWorkbook is subtle but important. The ThisWorkbook object refers to the workbook that the code is contained in. The ActiveWorkbook object refers to the workbook that is currently active. They often return the same object, but if the workbook running the code is not the active workbook, they return different objects. In this case, you don’t want to risk refreshing connections in other workbooks, so you use ThisWorkbook.

How to Use This Macro