Code For Excel And Outlook Blog

XML Parsing Function

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Function URLText(sURL As String) As String
' check website.com using xml
' early bound
 
Dim xSite As XMLHTTP60
 
Set xSite = New XMLHTTP60
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:

1
2
3
4
5
6
7
8
9
10
11

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