XML Parsing Function

May 28, 2008 by JP ·

In keeping with my Website XML parsing post, here is a short function that returns the body text from a webpage as a string, on which you can use other functions like Instr, Left$, Mid$, etc, to extract necessary data.

Function URLText(sURL As String) As String
' check website.com using xml
' early bound

Dim xSite As Object ' MSXML2.XMLHTTP60

Set xSite = CreateObject("MSXML2.XMLHTTP.6.0")
xSite.Open "GET", sURL, False
xSite.Send

Do Until xSite.readyState = 4
Loop

URLText = xSite.responseText

End Function

And it's as simple as that. You could even use it as a UDF. Here's a sample sub:

Sub CheckMySite()
Dim MyString As String
Dim i As Long

MyString = URLText("http://www.google.com")

If Instr(MyString, "Hello!") > 0 Then
    Cells(5,1).Value = Mid$(MyString, 1, 5)
End If

End Sub

This sub checks if the returned string contains the word "Hello" and if so, pulls the first five characters of the text and puts it in cell A5. It's just an arbitrary routine to show you what you can do with the XML response.

For my next trick, I will be showing you a routine that takes a snapshot of your Outlook inbox and writes it to a spreadsheet. I'm also planning on demonstrating some code that exports Contacts from Outlook to Excel.

Enjoy,
JP