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.
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.
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
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.
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