• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scraping json data from a javascript encrypted webpage using twofold request


Active Member
I've written a script which is able to produce links from javascript encrypted href from a webpage and then again using those newly produced links to get to the target page to scrape json response. I tried to do the whole thing flawlessly. It is working perfectly now. This thread is for those who get stuck at seeing json response and decide to quit. Here is the working code:
Sub Json_Twofold()
Const link = "https://www.aopa.org/learntofly/school/wsSearch.cfm?method=schoolDetail&businessId="
Dim http As New XMLHTTP60, str As Variant, exstr As Variant

With http
    .Open "GET", "https://www.aopa.org/learntofly/school/wsSearch.cfm?method=findSchools&searchTerm=All&radius=25", False
    str = Split(.responseText, "city"":""")
End With

y = UBound(str)
For i = 1 To y
    req = link & Split(Split(str(i), "businessId"":")(1), ",")(0)

    With http
        .Open "GET", req, False
        .setRequestHeader "content-type", "application/json;charset=UTF-8"
        exstr = Split(.responseText, "mgrLast"":""")
    End With
    Z = UBound(exstr)
    For L = 1 To Z
        x = x + 1
        Cells(x, 1) = Split(Split(exstr(L), "city"":""")(1), """")(0)
        Cells(x, 2) = Split(Split(exstr(L), "businessName"":""")(1), """")(0)
        Cells(x, 3) = Split(Split(exstr(L), "email"":""")(1), """")(0)
        Cells(x, 4) = Split(Split(exstr(L), "web"":""")(1), """")(0)
        Cells(x, 5) = Split(Split(exstr(L), "phone"":")(1), ",")(0)
    Next L
Next i
End Sub
Last edited: