How to Read Data from Online Web Pages with Excel VBA

This exercise shows how to read data from a website, in this case up-to-the-minute currency exchange rates from Yahoo.com.

It is assumed 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”.

Prerequisites include a knowledge of populating combo-boxes and defining names.

The workbook consists of two sheets:

The User Interface:

The Buy and Sell combo boxes reference the local data sheet below.

The table at D11 will be auto-pasted from the website itself, retaining user formatting.

The xlsm for this exercise can be downloaded here.

Building the Application Yourself.

Create a sheet with two combo boxes. The combo boxes will reference cells on a second sheet.

The combo-boxes will also invoke the code to read the website, using the Change event.

Currency names can be pasted into the second sheet (“Currencies”) from the list below, the sort-order being a matter of personal preference:

Paste the data into Columns B and E.

Define Names and use Index functions as per the illustration:

Since names have been defined on the local data sheet “Currencies”, sheet “Main” has merely to refer to the defined names to get the value, i.e. =SELL and =BUY for “Main” cells E10 and G10 respectively.

We will be programming Excel to read the web. It does have, however, its own built-in process on the Data ribbon, an instance of which you might like to record as a macro in building different scenarios. Note that computer or browser configurations can adversely affect web-page scripts.

The Code

Insert a module, and enter the following:

Option Explicit

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

  Range("D11:F14").ClearContents      'prepare the ground for the web data
  currBuy = Range("BUY")          'get variable values from Defined Names previously set up
  currSell = Range("SELL")
  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q?s=" & currBuy & currSell & "=X", Destination:=Range("$D$11"))
    .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
End sub

Sub DropDown2_Change()    'This event will call the routine above. Object name might differ.
    Call Ticker
End Sub

Sub DropDown1_Change()
    Call Ticker
End Sub

Save the workbook as type xlsm.

Test the code by changing the values of the combo boxes, then smarten up the formats of the pasted table (four decimal places, gridlines, shadings?).

Corruption of Excel files

Excel is known at times to corrupt files on saving, thereafter attempting its own self-recovery routine which often, in my experience, simply doesn’t work. This can be disastrous for the user, since it is the source file (possibly your only copy) that is destroyed. Damaged Excel files can however be repaired using third party tools, saving considerable time and effort.

Author Introduction:

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