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.
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment
'------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?
The following steps teach you how to put VBA code into a Standard Module:
- Activate the Visual Basic Editor by pressing ALT + F11.
- Right-click the project/workbook name in the Project Window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA