Web Queries are an excellent way of retrieving tables of information from web pages, but they are a little cumbersome if you are only interested in one or two items of information and are extremely susceptible to minor changes in the web page layout (such as adding an extra <table> tag at the top of the page). Another way is to read the page using a hidden instance of Internet Explorer, search within the page for the required information (using either the HTML or plain text representations), and then return the result. The following code requires a reference to the Microsoft Internet Controls object library:
Dim olE As SHDocVw.InternetExplorer Dim sPage As String Dim iGBP As Long, iDec As Long Dim iStart As Long, iEnd As Long Dim dRate As Double
'Create a new (hidden) instance of IE Set oIE = New SHDocVw.InternetExplorer
'Open the web page oIE.Navigate "http://www.x-rates.eom/d/USD/table.html"
'Wait for the page to complete loading Do Until olE.readyState = READYSTATE_COMPLETE DoEvents
'Retrieve the text of the web page into a variable sPage = olE.Document.body.InnerText
' Brazilian Real 2.1397 0.467355 ' British Pound 0.524934 1.905 ' Canadian Dollar 1.1056 0.904486
'To find the exchange rate, we have to find the entry for British 'Pounds, then work forwards to find the exchange rate
'Find the entry for British Pounds in the web page text. iGBP = InStr(1, sPage, "British Pound")
'Find the next decimal, which will be in the middle of the
'exchange rate number iDec = InStr(iGBP, sPage, ".")
'Find the start and end of the number iStart = InStrRev(sPage, " ", iDec) + 1 iEnd = InStr(iDec, sPage, " ")
'Evaluate the number, knowing that it's in US format dRate = Val(Mid$(sPage, iStart, iEnd - iStart))
'Display the rate
MsgBox "The USD/GBP exchange rate is " & dRate End Sub
The most appropriate method to use will depend on the precise circumstances, and how much data is required. For single items, it is probably easier to use the last approach. For more than a few items, it will be easier to use a Web Query to read the page or table into a workbook, and then find the required items on the sheet.
Was this article helpful?