Macro 10: Refresh All Data Connections in Workbook on 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 these
data connections automatically when the workbook is opened. This macro does the trick.
How it 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 work-
book.
Private Sub Workbook_Open()
‘Step 1: Use the RefreshAll method
Workbooks(ThisWorkbook.Name).RefreshAll
End Sub
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. 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 work-
books, so you use ThisWorkbook.
How to use it
To implement this macro, you need to copy and paste it into the Workbook_Open event code win-
dow. Placing the macro there 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 (see Figure 2-8).
5. Type or paste the code in the newly created module.
Figure 2-8: Enter or Paste your code in the Workbook_Open event code window.
