Web Scraping with VBA

My first hands on experience with web scraping was one of uncertainty and a significant amount of ‘on-the-job’ learning. Initially I was working as a tech support agent but once the operations manager caught wind of the fact that I’m a programmer, I was moved to the offline team and tasked with writing some sort of script that would scrape a relatively large amount of data from one of the company’s sites and store it in a spreadsheet for easier analysis.

I knew that this was definitely possible to do, it was just a matter of finding out how. I was given a week to determine if I was up to the task. I started where any sane person would, the Google search bar.

The first potential candidate I found was a Chrome plugin called webscraper.io. The examples they include on their site are very helpful and things were looking hopeful after a few hours of testing. The issue that led to this idea being scrapped was that it is only designed to scrape data from a website and has no functionality that would allow it to populate search fields and submit a form before commencing a scraping session. Although this tool didn’t suit my specific requirements, it could be of great help to someone looking to scrape an online store of all its products, for example.

Digging deeper and deeper through pages of Stack Overflow, I kept coming to the same conclusion; that my best bet of automatically navigating through webpages, populating search fields and trawling through pages of results, documenting the contents as I went, was to use Visual Basic for Applications (VBA).

I had no prior experience with VBA but there’s nothing particularly interesting about it or its syntax that caused me any trouble. I simply added references to ‘Microsoft Internet Controls’ and ‘Microsoft HTML Object Library’ and I had everything I needed within Excel and its IDE for VBA. The following code shows how to create an Internet Explorer object, set it to visible and navigate to a URL.


Set IE = New InternetExplorerMedium
IE.Visible = True
IE.navigate ""

Okay so now that we’ve covered how to get to the site primed for scraping, we need to figure out how to interact with elements within the page. What better way to showcase this than to attempt to login. The first thing to consider is how to address a particular element within a webpage. To do this we get the Document Object Model (DOM) and from there we can drill down to identify specific elements. Depending on the website in question and the prerogative of the developer(s) of said website, the complexity of this varies. The following code snippet works given the assumption that each element has been given a unique id.. sometimes this isn’t the case but I’ll talk about that in a moment. First, the easy way:


Dim doc: Set doc = IE.document

Dim passwordTextField = doc.getElementById("password")
passwordTextField.Value = "hunter2"

Dim submitBtn: Set submitBtn = doc.getElementById("submit")
submitBtn.Click

Looking at the below image, it’s clear to see how elements in a webpage are related. Each nested element is considered a child of its parent, meaning we can access any element by traversing through this tree structure.

An example of a DOM structure
Example DOM Structure

Let’s imagine that the case is the same as above but the submit button has not been given an id. The first place you ought to look is to the parent of that element. Let’s say that element is a

with an id of “submitBtnDiv”. In this instance we can still access the submit button a number of ways but it’s a little trickier:


' We can get the first element in the set of children elements
Dim submitBtn: set submitBtn = IE.document.getElementById("submitBtnDiv").Children(0)

If we can guarantee that the element will always be the nth instance of a given class, then we can do the following as well:


' If we know that the element in question is the first instance of an input tag
Dim submitBtn: set submitBtn = IE.document.getElementsByTagName("input")(0)

Note: using EI9 or later, you can also use the method getElementsByClassName().

Now that we can access any individual element within a webpage, writing a web-scraper just comes down to the individual site in question. For this example, let’s use IrishRail.ie. Quickly scanning through the source (using Google Chrome’s nifty inspect element feature), we can quickly determine how difficult it’s going to be to access the table of data we want.

html1

The first thing that jumps out at me here is that there’s a div with an id of ‘content’, that’s a starting point. The next thing I see is that the table tag is its 11th child. Already, we can access the table:


Dim table
' To get the 11th child element:
Set table = IE.document.getElementById("content").Children(10)

Now that we have the table, it’s just a matter of accessing each individual td and placing that value into the corresponding cell in your spreadsheet now. The following code shows a simple loop that will extract all data from the above table:


Dim trs, tds, tbody
tbody = table.getElementsByTagName("tbody")(0)
trs = tbody.getElementsByTagName("tr")

For r = 0 to trs.Length - 1
    tds = trs(r).getElementsByTagName("td")
    
    For c = - to tds.Length - 1
        ThisWorkbook.Sheets(1).Cells(r, c) = tds(c).Value
    Loop
Loop

In the above snippet, the getElementsByTagName method is used in two different ways. Firstly, we request the very first instance of tbody within the table tag. Secondly, we request a collection of elements of type tr. This gives us an ordered list of all tr tags nested within tbody. Looping through each row and then doing the same for each td, we can quickly grab all of the table’s contents without specifically referencing each individual piece of data.

And that’s basically all there is to it. There are other aspects one must think about when writing a web scraper that has to work reliably and consistently but this post should give you a basic understanding of the fundamental concepts used in web scraping.

2 thoughts on “Web Scraping with VBA”

  1. Hey Rich, I’m always on the look out for people learning to scrape using Code, to let you guys know about import.io

    You can build a crawler in less than 15 mins using point and click, and maybe is tiny amount of xPath. It’s free and there is a free udemy course my mate alex made: https://www.udemy.com/how-to-extract-data-from-the-web/

    If you want to learn how to code crawlers etc, thats cool, but the emerging industry trend is to use these tools to speed things up and remove the need for all the various overheads you will run into.

    1. Hey Dan,

      The requirements of this particular web scraping exercise include populating search fields, waiting for results to load, looping through pagination and also loading a ‘more details’ page to gather more information for each row returned. Can your tool do all of this?

      Also, using VBA I was able to fully automate the process (including selecting which certificate to send to the site to access different databases). I couldn’t find any preexisting tools that could perform this functionality.

      Regards,
      Rich

Leave a Reply to rich131murphy131 Cancel reply

Your email address will not be published. Required fields are marked *