shahin
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:
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
.send
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"
.send
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: