Screen Scraping 101 with VBA

No, not that kind of scraping. You can capture and download virtually any webpage using VBA. Here's one method for doing so.

I've written a simple function that lets you screen scrape almost any webpage you can pass as a parameter. I say "almost" because some pages are rendered in Javascript or Flash (or frames!) and don't really reveal any useful source code. The function will return the raw client-side output as a String with no parsing whatsoever. How you choose to parse the information is up to you.

The GetWebpage Function

This function should be familiar to anyone who has read any of my articles on XMLHTTP parsing. It uses the MSXML.XMLHTTP object to open and grab a web page. Simple as that. If you specify a filename as the second parameter, the webpage contents are written to a file.

Function GetWebpage(url As String, Optional fileName As String) As String

Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String

  Set xml = GetMSXML

  ' grab webpage
  With xml
    .Open "GET", url, False
    .send
  End With

  GetWebpage = xml.responseText

  ' write to file?
  If Len(fileName) > 0 Then
    If Not FileExists(fileName) Then
      Call CreateFile(fileName, GetWebpage)
    Else ' file exists
      If MsgBox("File already exists, overwrite?", vbYesNo) = vbYes Then
        Call CreateFile(fileName, GetWebpage)
      End If
    End If
  End If

End Function

The GetMSXML and CreateFile functions may be found on the MSXML Object Library page.

If you do choose to write the output to a file, you'll need this function:

Function FileExists(fileName As String) As Boolean
  FileExists = (Len(Dir(fileName)) > 0)
End Function

Sample usage

Sub TestGetWebpage()
  Dim webpage As String

  webpage = GetWebpage("http://www.jpsoftwaretech.com/blog/")
  Debug.Print webpage
End Sub