This article will walk you through how to create a straightforward Excel Macro to automatically refresh any external data connections and sources when your Excel file is opened. External Data sources that you could have linked to an Excel work books or work sheets are MS Query connections, Pivot Table connections or Web Queries.
So let’s get looking at this Macro.
What Does The Macro Do?
This macro will automatically refresh ANY data connections when you open your Excel spreadsheet file.
How Does The Macro Work?
This macro is a simple one line of VBA code that that uses the RefreshAll method to refresh all of the connections that are contained within your work book or work sheets. If you do have numerous external connections including Pivot Table connection in your Excel work book or work sheets then this will automatically refresh them all on your work book being opened.
This simple macro uses the ThisWorkbook object, which points to the current work book, this is slightly different to the ActiveWorkbook in that the ThisWorkbook is the work book that contains the code as opposed to the ActiveWorkbook which is any work book that is currently active and the user is using. This is a subtle difference but a really important one. See the difference between the tow?.
We definitely fo not want to refresh any connections in other Excel workbooks, hence we use ThisWorkbook object. OK so let’s get on and get this coding done.
FIRST. Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic. Find your Project/workbook name and expand the worksheets, select ThisWorkbook.
Step 1. – Select the Open event in the Event drop down list.
Step 2. – Use The RefreshAll method to refresh all data connections in This Work book
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
End Sub
It is easy to copy and paste the Excel VBA code into your Excel file and this applies to all sorts of VBA code that you may find online. Depending on the piece of code and what is it designed to do, you may copy it into a module, directly into a work book module, a worksheet module or a regular module.
In this example to copy the code begin by Opening Visual Basic by hitting F11 keyboard short cut or the select the Developer Tab – Then select Visual Basic.
Find your Project/work book name and expand the work sheets, select ThisWorkbook then paste the code.