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.

Function URLText(sURL As String) As String
  ' check website.com using xml
  Dim xSite As Object ' MSXML2.XMLHTTP60

  Set xSite = GetMSXML
  xSite.Open "GET", sURL, False
  xSite.Send

  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

Visit MSXML Object Library for the GetMSXML function.

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

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
learn excel dashboards