Microsoft Office Tutorials and References
In Depth Information
Parsing Web Pages for Specific Information
Web Queries are an excellent way of retrieving tables of information from Web pages, but
they’re a little cumbersome if you’re only interested in one or two items of information.
Another way is to read the page using a hidden instance of Internet Explorer, search within
the page for the required information, and then return the result. The following code
requires you to click the Tools, References command and select the Microsoft Internet
Controls object library:
Sub GetUSDtoCanadian()
Dim webIE As SHDocVw.InternetExplorer
Dim strPage As String
Dim lngCanadian As Long
Dim lngDec As Long
Dim lngStart As Long
Dim lngEnd As Long
Dim dblRate As Double
Set webIE = New SHDocVw.InternetExplorer
webIE.Navigate “http://www.fms.treas.gov/intn.html#rates"
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerHTML
lngCanadian = InStr(1, strPage, “CANADA - DOLLAR”)
lngDec = InStr(lngCanadian, strPage, “.”)
lngStart = InStrRev(strPage, “>", lngDec) + 1
lngEnd = InStr(lngDec, strPage, “<“)
dblRate = Val(Mid$(strPage, lngStart, lngEnd - lngStart))
MsgBox “The USD/Canadian exchange rate is “ & dblRate
End Sub
In determining whether to use a Web Query or parse a Web page, the most appropriate
method to use will depend on the precise circumstances and how much data is required.
For single items, it’s probably easier to search the Web page for the required information.
However, for more than a few items it’ll 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.
Search JabSto ::




Custom Search