Scraping information from a webpage not knowing it's last page number I've written some code in vba to scrape names and phone numbers from a webpage that has spread across some pages I don't wish to know of. The main interesting thing with this scraper is that It only needs to know the first page number then it traverse across all the pages and fetch the information I've mentioned above. I tried to make it error-free. Here is what I did:

Sub Yellowpage_Parser()

Const mlink = "https://www.yellowpages.com/search?search_terms=pizza&amp;geo_location_terms=San%20Francisco%2C%20CA&amp;page="
Dim http As New XMLHTTP60, html As New HTMLDocument
Dim post As HTMLHtmlElement

Do
    y = y + 1
    With http
        .Open "GET", mlink &amp; y, False
        .send
        html.body.innerHTML = .responseText
    End With

        For Each post In html.getElementsByClassName("info")
            With post.getElementsByClassName("n")(0).getElementsByTagName("span")
                If .Length Then x = x + 1: Cells(x, 1) = .item(0).innerText
            End With

            With post.getElementsByClassName("phones phone primary")
                If .Length Then Cells(x, 2) = .item(0).innerText
            End With
        Next post

Loop While InStr(http.responseText, "next ajax-page")

    Set html = Nothing
    MsgBox "Collected totals are " &amp; ActiveSheet.UsedRange.Rows.Count

End Sub
I tried to make it error-free.


Except this is the Web, and whether a server located somewhere on the globe will respond with something you expect, is completely out of your control.

This instruction:

.Open "GET", mlink &amp; y, False


Succeeds under normal circumstances, but one day the site will be down for maintenance, or whatever - and you'll get a run-time error, be it here or at the .Send call.

Code that doesn't handle errors is code that is written for the "happy path" - it's code that works well, until it doesn't. And then, when one thing goes wrong, everything burst up in flames, in a quite inelegant way. There are ways to graciously handle run-time errors.

Use them.

On Error GoTo CleanFail




Looks like you like scraping stuff. That's great, but at one point you need to solve the more generic problem, and move the URL from a local Const to a parameter.

Consider implementing the website-specifics as interface implementations.

@Interface
Option Explicit

Public Function Parse(ByVal url As String) As VBA.Collection
End Function


And when you start implementing interfaces in VBA, you'll find that VBA will refuse to compile when you have an implemented public interface member with an underscore in its name - so you might as well drop that habit now.



The indentation is wrong. Get the latest Rubberduck and use its Smart Indenter. Rubberduck will also warn you about other things, e.g. multiple declarations in a single instruction, single-letter identifiers, and As New, which literally makes an indestructible object - which is usually a very bad idea. Try adding this instruction:

Set http = Nothing
Debug.Print http Is Nothing


If you expected that to print True, you've fallen prey to the As New "feature". Best stay away from that when you want to control your objects' life time.