How to Update an Excel Worksheet Periodically with a VBA Timer

This article shows how to refresh a worksheet or chart at regular intervals. This exercise builds a timer that refreshes the worksheet every one minute, reflecting changes in the source data.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

An example of the workbook used in this exercise can be found here.

Uses of dynamic reporting might be:

• Reading the proprietary database supplied with your company telephone system, to analyse calls.
• Generate a random number every 15 minutes for prize or raffle purposes.
• Auto-send emails from your local database when certain levels are met. (here the timer will decide whether to fire the process or not, based on levels achieved).

or, the one we will use:

• Display the latest USD/EUR exchange rate from the internet.

The data is extracted from the Yahoo Finance website every one minute. Once successfully tested, a longer processing interval should be substituted.

The Interface

Open a new workbook;

Name the first sheet “Display”. Since the table D8 to E11 will be populated from the web, values or row-headings need not be entered. The rest is optional.

The Code

Open the VBA code window and insert a module. Copy the code below into the module.

The Auto_Open event starts the Timer when the workbook opens and stops when the worbook closes, unless the code is interrupted by the developer during development.

Sub Auto_Open()
AlertTime = Now + TimeValue("00:01:00") 'interval in minutes
Application.OnTime AlertTime, "Ticker"      'call Sub Ticker, below
End Sub

Sub Ticker()
    Dim currBuy As String
    Dim currSell As String

    Range("D8:F11").ClearContents            'prepare the destination for the web data
    currBuy = "USD"
    currSell = "EUR"
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q?s=" & currBuy & currSell & "=X", Destination:=Range("$D$8"))
        .Name = "q?s=" & currSell & currBuy & "=X"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """table1"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    AlertTime = Now + TimeValue("00:01:00") ‘move trigger on a minute
    Application.OnTime AlertTime, "Ticker"
End Sub

The Result

Every n minutes the table in Sheet “Display” will be updated from the Yahoo website. Refer to Microsoft’s website for more complex data connections.

Recovering corrupted workbooks

Excel has been known to crash at unexpected moments, probably related to a then lack of resources. In such cases the source file can be irrevocably damaged, and refuse to re-open.  In the absence of frequent backup, a tool to recover corrupted Excel files would be invaluable.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar fix and sql recovery software products. For more information visit www.datanumen.com