Happy Monday!, and it is Macro Time. Welcome to another #MacroMonday #Excel blog posts in my 2020 series. Today we will write a simple macro to refresh all data connections when your Excel workbook is opened.
Excel Data Sources.
External Data sources could be MS Query connections, Pivot Table connections or Web Queries. If you take a look at the Data Tab in the Connections Group you will see available data sources. Depending on your version of Excel, this may look slightly different,
What Does The Macro Do?
This macro will automatically refresh ANY data connections when you open your Excel workbook.
Preparing To Write The Macro Using ThisWorkbook Open Event.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11.
- or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
The macro uses the ThisWorkbook Open event, which points to the current workbook. This is slightly different to the ActiveWorkbook in that ThisWorkbook is the workbook that contains the code as opposed to the ActiveWorkbook which is any workbook that is currently active. See the difference?. The Syntax of the ThisWorkbook Open event is as follows
expression.Open
expression An expression that returns a Workbook object.
Starting The Macro.
As this code deals with a worksheet opening event, then it makes sense that the code needs to be not in a module as in a lot of VBA code, but within the code of the workbook itself. To enter the code into the relevant worksheet:
- Simply double click on ThisWorkbook in the VB Project Explorer.
- In the left-hand drop-down box select Worksheet
- In the right-hand drop-down box select Open
Excel starts the code off for us, and also enters the End Sub command at this stage too. All we need to do is write the rest of the code between these two lines.
[stextbox id=’info’]
Private Sub Workbook_Open()
End Sub
[/stextbox]
Refresh All Data Connections.
Next, we use a simple one line of code that uses the RefreshAll method to refresh all of the connections that are contained within your workbook or worksheet. If you have numerous connections or Pivot Tables then this will automatically refresh them all.
[stextbox id=’info’]
Workbooks(ThisWorkbook.Name).RefreshAll
[/stextbox]
We would not want to refresh connections in other workbooks, hence we use ThisWorkbook object. Ok so let’s get on and get this coding done and test it. That is my favourite part of writing this macro. This is a really great way to refresh all data connections in your Excel workbook.
If you want more tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
