[Solved] Parsing JSON data using VBA

shahin2137

Hi Ranjith, hope you are doing well. Recently working with a site I noticed that this very site uses data in json format which was impossible for me to scrape. I can see the data in the message but can't parse. Any guidance from your end would be a great help to me. Thanks in advance. I would like to parse: FirstName, LastName and city.

Sub WebData()
Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument
Dim PostData As String

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
    html.body.innerHTML = .responseText
End With
MsgBox http.responseText
End Sub

shahin2137

Following your instruction found within this forum when I run the below code, I get a compile error which also says "ambiguous name detected parsejson". Perhaps, I have messed up somewhere in my code.

Sub WebData()
Dim http As New MSXML2.XMLHTTP60
Dim PostData As String, JSON As Object, Item 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 JSON = ParseJson(.responseText)
End With
    For Each Item In JSON
        i = i + 1
        Cells(i, 1).Value = Item("firstName")
        Cells(i, 2).Value = Item("lastName")
        Cells(i, 3).Value = Item("city")
    Next Item
End Sub

shahin2137

Finally, found the solution. I am pasting the full code below for those who do stumble across such problem as i had to face earlier:

Sub JsonData()
Dim http As New MSXML2.XMLHTTP60
Dim PostData As String, JSON As Object
Dim results As VBA.Collection
Dim result As Scripting.Dictionary

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 JSON = JsonConverter.ParseJson(.responseText)
End With
MsgBox http.responseText
Set results = JSON("searchResults")
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
Set JSON = Nothing: Set results = Nothing: Set result = Nothing
End Sub