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.

Related Entries

  • Automatically Run an Excel Macro

    Auto run macro excel… Do you want to run an excel macros automatically when the file is open or close? Microsoft Excel provide us the opportunity to run a macro automatically every time we open or close the excel workbook file, using the event Workbook_Open and Workbook_BeforeClose. In the Microsoft Visual Basic Editor page, in the ThisWorkbook module, we simply...

  • Check If Excel Workbook is Already Open or Not

    In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not. The following excel vba function assigned to check whether a workbook we need is open...

  • Number of Cells/Rows/Columns With Formula

    A few weeks ago I received this question from someone called LS: Hi I want to write a macro that counts the following - Number of cells with formula - Number of Rows with formula - Number of Columns with formula Please let me know if there is a way of doing it in Excel VBA To find out the number of...

  • Automatically Protect/UnProtect All Worksheets

    Tonight I want to automatically protect and unprotect all Microsoft Excel Worksheets in my Workbook with password, instead of doing it manually, I put these following Excel VBA macro code in my Workbook.Public Sub ProtectAllSheets()Dim objSheet As Worksheet'Protecting all worksheets with password For Each...

  • VBA Delete Excel Rows Based on Certain Date

    Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date. The algorithm is like this: I’ll create a loop from the...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: excel macro refresh data, excel vba refresh external data, run a macro automatically say every 5 minutes, auto password protect when save and close in excel using vba, vba excel refreshALL, vba event on cell refresh, running excel macros automatically, refresh data in excel automatically, how to set a range automatically in excel count formula using vba, excel vba refresh sheet, excel vba events when received data, excel per minute refresh now(), excel macro: open a sheet using date, Excel Extract Data auto refresh, EXCEL 2007 CHART REFRESH WHILE MACRO IS RUNNING

«
»