Refresh All Data Connections/Queries In Excel

With the release of integrated stock pricing in Microsoft Excel, it was unfortunate there wasn’t any sort of setting that allowed for automatic updating of the stock data while the file was open.

Even worse, there is currently no way to ensure the data refreshes when you initially open your Excel file. This becomes vitally important if you are relying on your stock data to be up-to-date before making any trading decisions.

Let’s look at a few ways we can incorporate VBA code to help remedy the lack of this feature.

In this article, I’ll cover how to:

• Refresh your query data manually
• Refresh your data automatically when the file opens
• Refresh your data automatically when you select a specific tab
• Refresh your data at a given interval (ie every 30 seconds)

Refresh Data Connections Manually

You can trigger the refreshing of your stock data by either using keyboard shortcut Ctrl+Alt+F5 or navigating to your Excel Ribbon’s Data tab and clicking the Refresh All button within the Queries & Connections button group.

Refresh Data Connections When File Opens (VBA)

You can trigger a data refresh when your Excel file is first opened by pasting VBA code into the Workbook_Open event. Simply double-click the ThisWorkbook object in the VBA Project Pane to open the text editor (blank white sheet) within the Visual Basic Editor (keyboard shortcut Alt +F11).

Next, paste the below code into the text editor and save.

Private Sub Workbook_Open()
'PURPOSE: Run Data tab's Refresh All function when file is opened

ThisWorkbook.RefreshAll

MsgBox "Stock Data has been refreshed!"

End Sub

The next time you open your Excel file, you should see the message box immediately appear indicating that your stock data has been refreshed.

Refresh Data Connections When Sheet Is Activated (VBA)

You can trigger a data refresh when you navigate to a particular spreadsheet tab by pasting VBA code into the Worksheet_Activate event. Simply double-click the desired Sheet object in the VBA Project Pane to open the text editor (blank white sheet) within the Visual Basic Editor (keyboard shortcut Alt +F11). The tab name of your sheets will be inside the parathesis next to the object name, so you can easily decipher which sheet object you want to store the code in.

Next, paste the below code into the text editor and save.

Private Sub Worksheet_Activate()
'PURPOSE: Run Data tab's Refresh All function when sheet is activated

ThisWorkbook.RefreshAll

MsgBox "Stock Data has been refreshed!"

End Sub

The next time you navigate to the particular tab you stored the code in, you should see the message box immediately appear indicating that your stock data has been refreshed.

Refresh Data Connections Every X Seconds (VBA)

Alright, this is for all the day traders out there that want their data automatically refreshed throughout the day. We can utilize VBA’s OnTime functionality to schedule a macro to run and effectively create a loop so that it keeps refreshing at your desired interval.

There are 4 macros in the below code, however, you will only need to call two of them:

1. StartRefreshLoop – Starts the refresh intervals
2. EndRefreshLoop – Ends the refresh intervals

You can set the public variable “Seconds” to any interval you wish. Since stock data typically refreshes every 15 minutes, you’ll most likely want to set it to 960 seconds.

Public RefreshTime As Double
Public Const Seconds = 30 'Input Refresh Interval in seconds

Sub StartRefreshLoop()

'User Message indicating loop is beginning
  MsgBox "Refreshes will begin to occur at " & _
    "the designated interval of " & Seconds & " seconds"

'Call the first Refresh
  Call StartRefreshes

End Sub

Sub StartRefreshes()

'Calculate Next Refresh Time
  RefreshTime = Now + TimeSerial(0, 0, Seconds)

'Trigger a Refresh with OnTime function
  Application.OnTime _
    EarliestTime:=RefreshTime, _
    Procedure:="RefreshConnections", _
    Schedule:=True
  
End Sub

Sub RefreshConnections()

'Refresh Data Connections
  ThisWorkbook.RefreshAll

'Start Timer Over Again
  Call StartRefreshes

End Sub

Sub EndRefreshLoop()

'On Error Resume Next
  Application.OnTime _
    EarliestTime:=RefreshTime, _
    Procedure:="RefreshConnections", _
    Schedule:=False

'User Message indicating loop has ended
  MsgBox "Refreshes are no longer occurring"

End Sub

I Hope This Helped!

Hopefully, I was able to explain how you can use VBA code to automate refreshing your data connections to ensure you have the most up-to-date stock information in your spreadsheets. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Loop Through Every PivotTable With VBA

What This VBA Code Does There may be times when you need to carry out a VBA automation on all...

Turn Off Pivot Table Autofit Column Width On Update Setting

What This VBA Code Does If you live dangerously and have multiple Pivot Tables sporadically on a single sheet (I’ve...

The VBA Guide To Excel Pivot Tables [Tons Of Examples]

All About The Pivot Tables! Pivot Tables and VBA can be a little tricky initially.  Hopefully this guide will serve...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.