This article will walk you through how to create a simple Excel macro to automatically update external data sources and connections when your Excel file is opened. External data sources that you might have linked to Excel workbooks or worksheets are MS Query connections, PivotTable connections, or web queries.
So let’s look at this macro.
What does the macro do?
This macro will automatically update ANY data connection when you open your Excel spreadsheet file.
How does the macro work?
This macro is a simple line of VBA code that uses the RefreshAll method to update all the connections that are contained in your workbook or worksheets. If you have numerous external connections, including the pivot table connection in your Excel workbook or worksheets, this will automatically update them in your opening workbook.
This simple macro uses the ThisWorkbook object, which points to the current workbook, this is slightly different from ActiveWorkbook in that ThisWorkbook is the workbook that contains the code as opposed to ActiveWorkbook, which is any workbook that is currently active and the user is using. This is a subtle but very important difference. Do you see the difference between the trailer ?.
We definitely don’t want to update any connections in other Excel workbooks, therefore we use the ThisWorkbook object. Okay, let’s go ahead and finish this coding.
FIRST. Open Visual Basic by pressing F11 or the Developer – Visual Basic tab. Find your project / workbook name and expand worksheets, select This workbook.
Step 1. – Select the Open event from the Event drop-down list.
Step 2. – Use the RefreshAll method to update all data connections in this workbook
Here is the VBA code if you want to copy and paste it into your excel file.
Private Sub Workbook_Open ()
Workbooks (ThisWorkbook.Name) .RefreshAll
It’s easy to copy and paste Excel VBA code into your Excel file and this applies to all kinds of VBA code that you can find online. Depending on the code snippet and what it is designed for, you can copy it into a module, directly into a workbook module, a worksheet module, or a regular module.
In this example, to copy the code, start by opening Visual Basic by pressing the keyboard shortcut F11 or select the Developer tab – Then select Visual Basic.
Find your project / workbook name and expand the worksheets, select ThisWorkbook and then paste the code.