Excel Macro Tips – A Simple Macro To Refresh All Data Connections When Opening An Excel Workbook


Excel Macro

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.

Related Post – What Is Industrial Photography and Why Is It Important?

Source by BJ Johnston

What's Your Reaction?
Angry Angry
0
Angry
Confused Confused
0
Confused
Cry Cry
0
Cry
Cute Cute
0
Cute
Damn Damn
0
Damn
Dislike Dislike
0
Dislike
Fail Fail
0
Fail
Geeky Geeky
0
Geeky
OMG OMG
0
OMG
Scary Scary
0
Scary
Lol Lol
0
Lol
Like Like
0
Like
Love Love
0
Love
Win Win
0
Win
WTF WTF
0
WTF

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel Macro Tips – A Simple Macro To Refresh All Data Connections When Opening An Excel Workbook

log in

Captcha!
Don't have an account?
sign up

reset password

Back to
log in

sign up

Captcha!
Back to
log in
Choose A Format
Personality quiz
Trivia quiz
Poll
Story
List
Meme
Video
Audio
Image