ActiveWorkbook.RefreshAll <\/em><\/p>\n\n\n\n
With the release of integrated stock pricing in Microsoft Excel, it was unfortunate there wasn\u2019t 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\u2019s look at a few ways we can incorporate VBA code to help remedy the lack of this feature.<\/p>\n\n\n\n
\n\n\n\n
Refresh Data Connections Manually<\/strong><\/h2>\n\n\n\nYou can trigger the refreshing of your stock data by either using keyboard shortcut Ctrl+Alt+F5<\/strong> or navigating to your Excel Ribbon\u2019s Data tab<\/strong> and clicking the Refresh All button<\/strong> within the Queries & Connections button group<\/strong>.<\/p>\n\n\n\n![\"Refresh](\"https:\/\/images.squarespace-cdn.com\/content\/v1\/52b5f43ee4b02301e647b446\/1609559656572-W51PXG8NNRZI17MJ6C9O\/ke17ZwdGBToddI8pDm48kKnW2jUf34K1iq946rFyL9pZw-zPPgdn4jUwVcJE1ZvWQUxwkmyExglNqGp0IvTJZUJFbgE-7XRK3dMEBRBhUpx7H_-xjwk66IWNUm6eSaGXiNroAjXEANJ9L6IFbjuG4qx4Wju12Yo2D1TqcACo1XI\/Refresh+All+Button.png?format=750w\")
<\/figure>\n\n\n\n
\n\n\n\nRefresh Data Connections When File Opens (VBA)<\/strong><\/h2>\n\n\n\nYou can trigger a data refresh when your Excel file is first opened by pasting VBA code into the Workbook_Open<\/strong> event. Simply double-click the ThisWorkbook object <\/strong>in the VBA Project Pane<\/strong> to open the text editor (blank white sheet) within the Visual Basic Editor<\/strong> (keyboard shortcut Alt +F11<\/strong>).<\/p>\n\n\n\nNext, paste the below code into the text editor and save.<\/p>\n\n\n\n
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<\/p>\n\n\n\n![\"Workbook](\"https:\/\/images.squarespace-cdn.com\/content\/v1\/52b5f43ee4b02301e647b446\/1609559672323-4HXQ4GNJOQ5SGLYXTGCE\/ke17ZwdGBToddI8pDm48kM8-pqY656BtfuyqeD_7gvtZw-zPPgdn4jUwVcJE1ZvWhcwhEtWJXoshNdA9f1qD7T-j82ScS_xjTqFYGqFrT72iMt2Zx3C5zpDyHqH-h_uTr3I5Yn_PoKVTkBOPQXgEwQ\/Workbook+Event.png?format=300w\")
<\/figure>\n\n\n\nThe next time you open your Excel file, you should see the message box immediately appear indicating that your stock data has been refreshed.<\/p>\n\n\n\n
\n\n\n\nRefresh Data Connections When Sheet Is Activated (VBA)<\/strong><\/h2>\n\n\n\nYou can trigger a data refresh when you navigate to a particular spreadsheet tab by pasting VBA code into the Worksheet_Activate<\/strong> event. Simply double-click the desired Sheet object<\/strong> in the VBA Project Pane<\/strong> to open the text editor (blank white sheet) within the Visual Basic Editor<\/strong> (keyboard shortcut Alt +F11<\/strong>). 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.<\/p>\n\n\n\nNext, paste the below code into the text editor and save.<\/p>\n\n\n\n
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<\/p>\n\n\n\n![\"Worksheet](\"https:\/\/images.squarespace-cdn.com\/content\/v1\/52b5f43ee4b02301e647b446\/1609559690161-YUSXU03YOO5YM4SDWCT7\/ke17ZwdGBToddI8pDm48kBs0ocHOzau24t9H4uusMX1Zw-zPPgdn4jUwVcJE1ZvWhcwhEtWJXoshNdA9f1qD7WT60LcluGrsDtzPCYop9hMAMnD_t6Osls2WhQJMPj7yGu8fDB-vRfNuRqjC_EFtaQ\/Worksheet+Event.png?format=300w\")
<\/figure>\n\n\n\nThe 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.<\/p>\n\n\n\n
\n\n\n\nRefresh Data Connections Every X Seconds (VBA)<\/strong><\/h2>\n\n\n\nAlright, this is for all the day traders out there that want their data automatically refreshed throughout the day. We can utilize VBA\u2019s OnTime functionality to schedule a macro to run and effectively create a loop so that it keeps refreshing at your desired interval.<\/p>\n\n\n\n
There are 4 macros in the below code, however, you will only need to call two of them:<\/p>\n\n\n\n
- StartRefreshLoop – Starts the refresh intervals<\/li>
- EndRefreshLoop – Ends the refresh intervals<\/li><\/ol>\n\n\n\n
You can set the public variable \u201cSeconds\u201d to any interval you wish. Since stock data typically refreshes every 15 minutes, you\u2019ll most likely want to set it to 960 seconds.<\/p>\n\n\n\n
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<\/em><\/p>\n\n\n\n
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<\/em><\/p>\n\n\n\nRef: https:\/\/www.thespreadsheetguru.com\/blog\/vba-refresh-data-connections-excel<\/p>\n","protected":false},"excerpt":{"rendered":"
Quick reference: The first one will “refresh” only one file. The second will “refresh” all open excel files. ThisWorkbook.RefreshAllActiveWorkbook.RefreshAll With the release of integrated stock pricing in Microsoft Excel, it was unfortunate there wasn\u2019t any sort of setting that allowed for automatic updating of the stock data while the file was open. Even worse, there Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[705,706],"class_list":["post-3292","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-refresh-data-connections-in-excel-vba","tag-refresh-data-connections-vba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3292"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292\/revisions"}],"predecessor-version":[{"id":3296,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292\/revisions\/3296"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
<\/figure>\n\n\n\n\n\n\n\n
Refresh Data Connections When File Opens (VBA)<\/strong><\/h2>\n\n\n\nYou can trigger a data refresh when your Excel file is first opened by pasting VBA code into the Workbook_Open<\/strong> event. Simply double-click the ThisWorkbook object <\/strong>in the VBA Project Pane<\/strong> to open the text editor (blank white sheet) within the Visual Basic Editor<\/strong> (keyboard shortcut Alt +F11<\/strong>).<\/p>\n\n\n\nNext, paste the below code into the text editor and save.<\/p>\n\n\n\n
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<\/p>\n\n\n\n<\/figure>\n\n\n\nThe next time you open your Excel file, you should see the message box immediately appear indicating that your stock data has been refreshed.<\/p>\n\n\n\n
\n\n\n\nRefresh Data Connections When Sheet Is Activated (VBA)<\/strong><\/h2>\n\n\n\nYou can trigger a data refresh when you navigate to a particular spreadsheet tab by pasting VBA code into the Worksheet_Activate<\/strong> event. Simply double-click the desired Sheet object<\/strong> in the VBA Project Pane<\/strong> to open the text editor (blank white sheet) within the Visual Basic Editor<\/strong> (keyboard shortcut Alt +F11<\/strong>). 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.<\/p>\n\n\n\nNext, paste the below code into the text editor and save.<\/p>\n\n\n\n
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<\/p>\n\n\n\n<\/figure>\n\n\n\nThe 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.<\/p>\n\n\n\n
\n\n\n\nRefresh Data Connections Every X Seconds (VBA)<\/strong><\/h2>\n\n\n\nAlright, this is for all the day traders out there that want their data automatically refreshed throughout the day. We can utilize VBA\u2019s OnTime functionality to schedule a macro to run and effectively create a loop so that it keeps refreshing at your desired interval.<\/p>\n\n\n\n
There are 4 macros in the below code, however, you will only need to call two of them:<\/p>\n\n\n\n
- StartRefreshLoop – Starts the refresh intervals<\/li>
- EndRefreshLoop – Ends the refresh intervals<\/li><\/ol>\n\n\n\n
You can set the public variable \u201cSeconds\u201d to any interval you wish. Since stock data typically refreshes every 15 minutes, you\u2019ll most likely want to set it to 960 seconds.<\/p>\n\n\n\n
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<\/em><\/p>\n\n\n\n
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<\/em><\/p>\n\n\n\nRef: https:\/\/www.thespreadsheetguru.com\/blog\/vba-refresh-data-connections-excel<\/p>\n","protected":false},"excerpt":{"rendered":"
Quick reference: The first one will “refresh” only one file. The second will “refresh” all open excel files. ThisWorkbook.RefreshAllActiveWorkbook.RefreshAll With the release of integrated stock pricing in Microsoft Excel, it was unfortunate there wasn\u2019t any sort of setting that allowed for automatic updating of the stock data while the file was open. Even worse, there Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[705,706],"class_list":["post-3292","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-refresh-data-connections-in-excel-vba","tag-refresh-data-connections-vba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3292"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292\/revisions"}],"predecessor-version":[{"id":3296,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292\/revisions\/3296"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Next, paste the below code into the text editor and save.<\/p>\n\n\n\n
Private Sub Workbook_Open() The next time you open your Excel file, you should see the message box immediately appear indicating that your stock data has been refreshed.<\/p>\n\n\n\n You can trigger a data refresh when you navigate to a particular spreadsheet tab by pasting VBA code into the Worksheet_Activate<\/strong> event. Simply double-click the desired Sheet object<\/strong> in the VBA Project Pane<\/strong> to open the text editor (blank white sheet) within the Visual Basic Editor<\/strong> (keyboard shortcut Alt +F11<\/strong>). 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.<\/p>\n\n\n\n Next, paste the below code into the text editor and save.<\/p>\n\n\n\n Private Sub Worksheet_Activate() 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.<\/p>\n\n\n\n Alright, this is for all the day traders out there that want their data automatically refreshed throughout the day. We can utilize VBA\u2019s OnTime functionality to schedule a macro to run and effectively create a loop so that it keeps refreshing at your desired interval.<\/p>\n\n\n\n There are 4 macros in the below code, however, you will only need to call two of them:<\/p>\n\n\n\n You can set the public variable \u201cSeconds\u201d to any interval you wish. Since stock data typically refreshes every 15 minutes, you\u2019ll most likely want to set it to 960 seconds.<\/p>\n\n\n\n Public RefreshTime As Double Ref: https:\/\/www.thespreadsheetguru.com\/blog\/vba-refresh-data-connections-excel<\/p>\n","protected":false},"excerpt":{"rendered":" Quick reference: The first one will “refresh” only one file. The second will “refresh” all open excel files. ThisWorkbook.RefreshAllActiveWorkbook.RefreshAll With the release of integrated stock pricing in Microsoft Excel, it was unfortunate there wasn\u2019t any sort of setting that allowed for automatic updating of the stock data while the file was open. Even worse, there Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[705,706],"class_list":["post-3292","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-refresh-data-connections-in-excel-vba","tag-refresh-data-connections-vba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3292"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292\/revisions"}],"predecessor-version":[{"id":3296,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3292\/revisions\/3296"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
‘PURPOSE: Run Data tab’s Refresh All function when file is opened
ThisWorkbook.RefreshAll
MsgBox “Stock Data has been refreshed!”
End Sub<\/p>\n\n\n\n<\/figure>\n\n\n\n
\n\n\n\nRefresh Data Connections When Sheet Is Activated (VBA)<\/strong><\/h2>\n\n\n\n
‘PURPOSE: Run Data tab’s Refresh All function when sheet is activated
ThisWorkbook.RefreshAll
MsgBox “Stock Data has been refreshed!”
End Sub<\/p>\n\n\n\n<\/figure>\n\n\n\n
\n\n\n\nRefresh Data Connections Every X Seconds (VBA)<\/strong><\/h2>\n\n\n\n
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<\/em><\/p>\n\n\n\n
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<\/em><\/p>\n\n\n\n