XML Parsing Function

May 28, 2008 – 1:58 am 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.

plain text version

VBA:

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:

plain text version

VBA:

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

If you enjoyed this page:

Print This Post |

Email This Post | Permalink |

Subscribe now!

Filed Under: Excel, Internet Explorer, VBA, automation
Tags: Excel, Internet Explorer, parsing, VBA, XML

This post has 273 views since May 28, 2008 – 1:58 am.

One Response to “XML Parsing Function”
Looks like I forgot to mention you will need to set a reference to Microsoft XML, in my case (Excel 2003) it is version 6.0. The filename in Windows XP is c:\windows\system32\msxml6.dll.

--JP

By JP on May 30, 2008

Subscribe without commenting
E-Mail:

Keep Reading:

Browse Posts:

« Save Incoming Attachments, Choose Your Folder || Open Any Email Attachment From Outlook »