CODEWITHSUNDEEP
jsonvbaweb-scraping
SIM
SIM

Reputation: 22440

Unable to fetch data which are in json format from a webpage

After running my vba script for the purpose of parsing data from a webpage I could see that it shows "object required" error. I can see the desired data in the msgbox which is set before the error causing line. As i haven't worked with json format yet, I can't make the execution successful. Any help would be appreciated. Here is what i'm up to:

Sub JsonData()
Dim http As New MSXML2.XMLHTTP60
Dim PostData As String, JSONa As Object, ele As Object

PostData = "region=US&latitude=61.7958256&longitude=-148.8045856&location=Sutton-Alpine%2C%20AK&source=US-STANDALONE&radius=25&pageNumber=1&pageSize=10&sortBy=&industryFilter=340&serviceFilter=550,90"

With http
    .Open "GET", "https://proadvisorservice.intuit.com/v1/search?" & PostData, False
    .setRequestHeader "Content-Type", "application/json; charset=utf-8"
    .setRequestHeader "Accept", "application/json;version=1.1.0"
    .send
    Set JSONa = JsonConverter.ParseJson(.responseText)
End With
MsgBox http.responseText
    For Each ele In JSONa
        i = i + 1
        Cells(i, 1).Value = ele("firstName")
        Cells(i, 2).Value = ele("lastName")
        Cells(i, 3).Value = ele("city")
    Next ele
End Sub

Upvotes: 1

Views: 52

Answers (1)

gembird
gembird

Reputation: 14053

The search results are VBA.Collection where each itemof this collection contains then another Scripting.Dictionary. Hope what you ask for is the following. HTH 

Dim results As VBA.Collection
Set results = JSONa("searchResults")

Dim result As Scripting.Dictionary
For Each result In results
    i = i + 1
    Cells(i, 1).Value = result("firstName")
    Cells(i, 2).Value = result("lastName")
    Cells(i, 3).Value = result("city")
Next result

Upvotes: 1

Related Questions