[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