In this article I will explain how to retrieve data from a website using a query table and VBA.

Previously in the article Excel Getting Data From the Web I’ve explained how you can use Query Tables to import data from the web into an excel worksheet. In this article it is assumed readers are familiar with using query tables.

Lets say similar to the article Excel Getting Data From the Web we want to get the USD to CAD currency exchange rate using a query table. Only this time we want to get it done using VBA. Basically what we want to do is to connect to the website http://finance.yahoo.com/q?s=usdCAd=x using a query table and retrieve the 4 numbers marked below:

Note: If the layout of the website changes, we may need to update our code.

Step 1, Record Macro:

The first step would be to record the steps used in the article Excel Getting Data From the Web using the macro recorder. You will end up with a code similar to the one below:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q?s=usdCAd=x", _
        Destination:=Range("$A$1"))
        .Name = "q?s=usdCAd=x_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub


Understanding the Recorded Macro:

The first line of code creates a new worksheet. This is only necessary if you want the Query Table to appear in a new sheet:

ActiveWorkbook.Worksheets.Add


The next 3 lines of code are very important:

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q?s=usdCAd=x", _
    Destination:=Range("$A$1"))


Insertion Location:

The highlighted parts below define the location the query table will be inserted. The default code inserts the query table in cell A1 in the active sheet:

With ActiveSheet.QueryTables.Add(Connection:= _
   "URL;http://finance.yahoo.com/q?s=usdCAd=x", _
   Destination:=Range("$A$1"))


We could change the code above to insert the query table in a different location. For example the code below will insert the query table  in cell B2 of sheet3:

With Sheet3.QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q?s=usdCAd=x", _
    Destination:=Sheet3.Range("$B$2"))


We could also use the notation below. Assuming the name of sheet3 is “Sheet3′:

With Sheets("Sheet3").QueryTables.Add(Connection:= _
    "URL;http://finance.yahoo.com/q?s=usdCAd=x", _
    Destination:=Sheet3.Range(Sheet3.Cells(2, 2), _
    Sheet3.Cells(2, 2)))


For more information about the different methods of referring sheets in VBA please see the link below:

• Excel VBA, Working With Sheets

Website URL:

The highlighted part below defines the URL of the website to connect to:

With ActiveSheet.QueryTables.Add(Connection:= _
   "URL;http://finance.yahoo.com/q?s=usdCAd=x", _
   Destination:=Range("$A$1"))


You can change the highlighted text to any other website, for example the code below will connect to the website http://msdn.microsoft.com/en-US/:

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://msdn.microsoft.com/en-US/", _
    Destination:=Range("$A$1"))


Note: If you want to use a string variable to define the website address,  you must add the string “URL;” to the start of the string. For example:

Dim strAddress As String
strAddress = "http://msdn.microsoft.com/en-US/"
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & strAddress, _


Step 2:

In Step 1 we created a data query which gets data from the website http://finance.yahoo.com/q?s=usdCAd=x. As explained in the article Excel Getting Data From the Web we will end up with a sheet full of data. We need to figure out which cell contains the data we are looking for.

Assume we used the code below:

With Sheet1.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=usdCAd=x", Destination:=Sheet1.Range("$A$1"))
.Name = "q?s=usdCAd=x_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

The data we are after (currency exchange rates) will appear in the cells B120 to B123:

We could then use a code similar to that below to get the required values:

Dim dblPrevClose As Double
Dim dblOpen As Double
Dim dblBid As Double
Dim dblAsk As Double

dblPrevClose = Sheet1.Cells(120, 2)
dblOpen = Sheet1.Cells(121, 2)
dblBid = Sheet1.Cells(122, 2)
dblAsk = Sheet1.Cells(123, 2)


 Also I have provided a complete tutorial on making a FOREX (currency exchange) sheet in the article Excel VBA, Currency Exchange (Forex) Sheet, (Sample Code and Download)

You can download the file and code used in this article from the link below:

• Data Query.xlsm

See also:

• Excel Getting Data From the Web
• GetElementByID in VBA

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com