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 pivot table connection in your excel workbook or worksheets, this will automatically update them in your workbook that is opened.

This simple macro uses the ThisWorkbook object which points to the current workbook, this is slightly different to 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 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: Visual Basic tab. Find your project/workbook name and expand the worksheets, select ThisWorkbook.

Step 1. – Select the Open event in 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 applies to all kinds of VBA code you can find online. Depending on the code snippet and what it’s 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.