Schedules to Refresh Data Automatically using VBA Macro
I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook.
Below is a sample of how we can do this in Microsoft Excel *just an outline*.
The logic is simple, when we open the Workbook, we call for the first time the procedure needed to refresh the data. In that procedure, we also add a code to call back the procedure itself 15 minutes later. So while the Workbook is still open, the procedure will continue to call itself every 15 minutes.
' Put this procedure in ThisWorkbook module
Private Sub Workbook_Open()
' Run the procedure to refresh data when the workbook opened
Call RefreshData
End Sub
' Add new module and put this procedure in it
Public Sub RefreshData()
Debug.Print "Data refreshed at " & Now
'
' This is where we put everything needed to refresh the data
'
' Schedules this procedure to be run 15 minutes from now
Application.OnTime Now + TimeValue("00:15:00"), "RefreshData"
End Sub
Even looks like an infinite loop, this is not a problem for Excel when we use Application.OnTime function. The scheduling will stop automatically when the Workbook is close.
