A Simple Macro Examples
In this article, you will learn how to create a simple Excel macro to automatically update external data connections and sources when you open your Excel file. External data sources that you may have linked to Excel workbooks or worksheets are MS Query Connections, Pivot Table Connections, or Web Queries.
So let’s take a look at this macro.
What does the macro do?
This macro automatically updates all data connections when you open your Excel worksheet file.
How does the macro work?
This macro is a simple line of VBA code that uses the RefreshAll method to update all of the links that are contained in your workbook or in your worksheets. If you have a large number of external connections, including the pivot table connection, in your Excel workbook or worksheets, they are automatically updated in your open workbook.
This simple macro uses the ThisWorkbook object, which references the current workbook. This is slightly different from ActiveWorkbook in that ThisWorkbook is the workbook that contains the code, not the ActiveWorkbook, which is a currently active workbook. This is a subtle but very important difference. See the difference between the tow.
We definitely do not want to update links in other Excel workbooks, so we use the ThisWorkbook object. OK, so let’s go ahead and do this coding.
FIRST. Open Visual Basic by pressing F11 or Developer Tab – Visual Basic. Find the name of your project / workbook, expand the worksheets, and select ThisWorkbook.
Step 1. – Select the Open event from the Event drop-down list.
Step 2. – Refresh all data connections in this workbook using the RefreshAll method
Here is the VBA code if you want to copy it and paste it into your Excel file.
Private Sub Workbook_Open ()
It’s easy to copy the Excel VBA code and paste it into your Excel file. This applies to all types of VBA code that you can find online. Depending on your code and purpose, you can copy it to a module, directly into a workbook module, worksheet module, or regular module.
In this code copy example, first open Visual Basic by pressing F11, or select the Developer tab. Then select Visual Basic.
Find the name of your project / workbook, expand the worksheets, select ThisWorkbook, and paste the code.