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.

Macro Code (VBA Code)

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.

What's the difference between ThisWorkbook and 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

To implement this macro, you need to copy and paste it into the Workbook Open event code window. Placing the macro here allows it to run each time the workbook opens.

  1. Activate the Visual Basic Editor by pressing ALT+F11.
  2. In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.
  3. Click ThisWorkbook.
  4. Select the Open event in the Event drop-down list.Workbook Open Event
  5. Type or paste the code in the newly created module..

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>