In this article, you’ll learn 4 suitable examples of using Excel VBA to refresh all data connections.
The examples included refreshing the dataset when opening a workbook, activating a worksheet and after every fixed interval, or refreshing a Pivot Table using VBA.
Before that, you’ll learn a quick method to create data connections between Excel files from the Data tab.
You can use these methods to automate the process of refreshing data connections in your Excel workbook. This is particularly useful when you have multiple connections that need to be refreshed regularly, such as daily or weekly updates regarding sales or stocked data.
Below you’ll see an overview image showing the use of Excel VBA to refresh all data connections.
How to Create Data Connections between Excel Files
Let’s have a quick review of how to create data connections between Excel files. We’ll show here one method using the Data tab. If you want to know about other methods, follow this article: How to Create Excel Data Connection to Another Excel File.
- We have our original dataset in the sheet named Dataset of Excel file named Source Sheet.
- We’ll upload this dataset to a new Excel file named Refreshed Sheet.
- Open the Workbook Refreshed Sheet and go to Data > Get Data > From File > From Excel Workbook.
- Choose the file you want to upload from the folder on your computer.
- We choose Source Sheet from our New Volume (F:) folder and click on Import.
- The Navigator window will appear.
- Select the sheet name from where you want to upload your dataset. Our sheet name is Dataset.
- Then, select the icon beside the Load menu and click on Load To.
- The Import Data window will appear.
- Choose Table as to how you want to view your data.
- Give the position of the dataset by choosing the Existing worksheet: option and giving the position as cell B2.
- Press OK to finish.
- Your data will be uploaded to the workbook Refreshed Sheet in a table format. It’ll be connected with the original workbook Source Sheet.
- You can format the dataset to make it visually clear.
Refresh All Data Connections Using Excel VBA: 4 Suitable Examples
Now, we’ll be concerned about our original topic of refreshing all data connections using Excel VBA. We’ll discuss 4 examples related to this topic below.
Example 1: Refreshing When Opening Workbook
You can refresh your data connections of the Excel file as soon as you open the workbook automatically. For this, we’ll put a simple VBA code and assign it to trigger an event called Workbook Open in VBA.
- Open the workbook Refreshed Sheet and go to Developer > Visual Basic.
- Or use the keyboard shortcut Alt + F11 after opening the workbook Refreshed Sheet to directly open the Visual Basic window.
- Double-click on the ThisWorkbook object.
- Choose Workbook and Open options from the Module and write the following code there.
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
MsgBox "Total Sales Data has been Refreshed"
End Sub- Save the code by pressing the Save button or Ctrl + S shortcut. You don’t have to run the code.
- Close the workbook Refreshed Sheet.
- Open the original workbook Source Sheet and change the first 2 Salesman’s names.
- Save and close the original workbook.
- As soon as you open the workbook Refreshed Sheet, you’ll see a message like this.
- Press OK to continue.
- The dataset is refreshed automatically.
Example 2: Refreshing When Activating Worksheet
We want to activate the sheet called Activating Worksheet in the workbook Refreshed Sheet and see the refreshed dataset. So, we’ll write a simple code and assign it to trigger the VBA event Worksheet Activate.
- Select Visual Basic under the Developer tab from the sheet called Activating Worksheet in the workbook Refreshed Sheet.
- Alternatively, you can open the Visual Basic window instantly by using the keyboard shortcut Alt + F11.
- Double-click on the Sheet2 (Activating Worksheet) object.
- Choose Worksheet and Activate options from the Module and write the following code there.
Private Sub Worksheet_Activate()
ThisWorkbook.RefreshAll
MsgBox "Total Sales Data has been Refreshed"
End Sub- By using the shortcut Ctrl + S or the Save button, you can save the code. The code need not be executed.
- Close the workbook Refreshed Sheet.
- Change the first 2 Salesman’s names on the Source Sheet workbook. After that, save the workbook and close it.
- As soon as you open the workbook Refreshed Sheet and activate the sheet titled Activating Worksheet, you’ll see a message like this.
- Press OK to continue.
- The dataset is automatically updated.
Example 3: Refreshing After Every Fixed Interval
Suppose you want to refresh your dataset after every fixed interval say 60 seconds or 1 minute. You can easily do this by putting a VBA code.
- Select Visual Basic under the Developer tab from the workbook Refreshed Sheet.
- Select Module from the Insert menu in the Visual Basic window.
- Enter the following code in the Module and click the Run button or press F5 from the keyboard to run the code.
Public ReloadInterval As Double
Public Const Period = 60
Sub Reload()
MsgBox "Total Sales Data will be Refreshed at " & _
"the interval of " & Period & " seconds"
Call FirstReload
End Sub
Sub FirstReload()
ReloadInterval = Now + TimeSerial(0, 0, Period)
Application.OnTime _
EarliestTime:=ReloadInterval, _
Procedure:="ReloadConnections", _
Schedule:=True
End Sub
Sub ReloadConnections()
ThisWorkbook.RefreshAll
Call FirstReload
End Sub
- The Macros window will open.
- Select Reload macro from there and press Run.
- You’ll get the following message. Press OK to continue.
- On the Source Sheet workbook, change the first two Salesmen’s names. The workbook should then be saved and closed.
- Wait for 60 seconds and open the Refreshed Sheet workbook. You’ll find the refreshed data.
- It’ll be refreshed automatically after every 60 seconds or 1 minute.
Read More: How to Refresh Data Connection in Excel Without Opening File
Example 4: Refreshing a Pivot Table
We can also refresh a Pivot Table by running a VBA code.
- We have a Pivot Table created with our dataset as you can see below.
- After changing the first two Salesmen’s names, the data of the Pivot Table has not changed.
- So, we have to refresh it.
- Select Visual Basic under the Developer tab to open the Visual Basic window.
- From the Insert menu in the Visual Basic window, choose Module.
- To run the code, type the following into the Module and either click Run or hit F5 on your keyboard.
Sub Refreshing_Pivot_Table()
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub
- After running the code, you’ll see that the Pivot Table has been refreshed.
Read More: [Fixed]! Data Connection Not Refreshing in Excel
Things to Remember
- We are working with 2 workbooks here named Source Sheet and Refreshed Sheet. The Source Sheet workbook is saved in xlsx format and the Refreshed Sheet workbook is saved in xlsm format.
- The shortcut key for opening the Visual Basic window is Alt + F11.
- The shortcut key for running the VBA code is F5.
Frequently Asked Questions
1. How do I refresh all data connections manually in Excel?
Go to the Data tab and in the Connections group, click on the Refresh All button. Once the refresh is complete, the data from all the connections will be updated. You can also use the keyboard shortcut Ctrl+Alt+F5 to refresh your data connections manually.
2. How do I refresh multiple connections in Excel?
Go to the Data tab and in the Connections group, click on the Connections button. This will open the Workbook Connections window. In the window, select the connections you want to refresh. You can use Ctrl+Click or Shift+Click to select multiple connections. After selecting the desired connections, click on the Refresh button. Excel will refresh the selected connections, and the updated data will be shown in your workbook.
Download Practice Workbook
You can download the Excel files from here for practice.
Conclusion
In conclusion, you have learned to use Excel VBA to automate the process of refreshing data connections in your workbook through this article. The article has shown different examples regarding refreshing data when opening a workbook, activating a worksheet, or at fixed intervals. Additionally, the ability to refresh Pivot Tables using VBA enhances your data analysis power. Please let us know in the comment section if there is any query or suggestions related to this topic.
