[Solved] Parsing JSON data using VBA



  • 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


  • 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


  • 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

Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.