Excel Macro Tips – A simple macro to update all data connections when opening an Excel workbook
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 take a 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 single line of VBA code that uses the RefreshAll method to refresh all connections that are contained within your workbook or worksheets. If you have numerous external connections, including PivotTable connection in your Excel workbook or worksheets, this will automatically update them in your workbook that opens.
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 whatever workbook is currently active and the user is using. This is a subtle but really important difference. Do you see the difference between the trailer?.
We definitely don’t want to update any connections in other Excel workbooks, so we use the ThisWorkbook object. Okay, let’s go ahead and do this coding.
FIRST. Open Visual Basic: Press F11 or the Developer tab: Visual Basic. Find your project/workbook name and expand the worksheets, select ThisWorkbook.
Step 1. – Select the Open event from the Event dropdown list.
Step 2. – Use the RefreshAll method to refresh all data connections in this workbook
Here is the VBA code if you want to copy and paste it into your excel file.
private subworkbook_Open()
Workbooks(ThisWorkbook.Name).RefreshAll
finish sub
It’s easy to copy and paste Excel VBA code into your Excel file, and this goes for all kinds of VBA code you can find online. Depending on the code snippet and what it’s designed to do, 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 F11 keyboard shortcut 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.