Excel VBA - How to get data from multiple-array JSON into columns

Question

I found a solution for parsing JSON, and it works fine for the presented example:

Here's the code:

 Sub Test() Dim jsonText As String Dim jsonObj As Dictionary Dim jsonRows As Collection Dim jsonRow As Collection Dim ws As Worksheet Dim currentRow As Long Dim startColumn As Long Dim i As Long Set ws = Worksheets("VIEW") 'Create a real JSON object jsonText = ws.Range("A1").Value 'Parse it Set jsonObj = JSON.parse(jsonText) 'Get the rows collection Set jsonRows = jsonObj("rows") 'Set the starting row where to put the values currentRow = 1 'First column where to put the values startColumn = 2 'B 'Loop through all the values received For Each jsonRow In jsonRows 'Now loop through all the items in this row For i = 1 To jsonRow.Count ws.Cells(currentRow, startColumn + i - 1).Value = jsonRow(i) Next i 'Increment the row to the next one currentRow = currentRow + 1 Next jsonRow End Sub 

And the JSON that is working:

{"rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]} 

However I need to parse JSON that has a structure like this:

 [{"Id":"2604","Price": 520.4, "State": true},{"Id":"2605","Price": 322.8, "State": false},{"Id":"2619","Price": 104.7, "State": true},{"Id":"2628","Price": 182.2, "State": true}] 

That means, in this case, It should be 3 columns (Id, Price, Status) and 4 rows.

It should be easy but I am just a total newbie here..

Answer 1

Should be something like this:

Dim jsonRows As Collection Dim jsonRow As Dictionary '... 'Parse it Set jsonRows = JSON.parse(jsonText) 'Set the starting row where to put the values currentRow = 1 'First column where to put the values startColumn = 2 'B 'Loop through all the values received For Each jsonRow In jsonRows 'Now set all the values in this row ws.Cells(currentRow, startColumn).Value = jsonRow("Id") ws.Cells(currentRow, startColumn + 1).Value = jsonRow("Price") ws.Cells(currentRow, startColumn + 2).Value = jsonRow("State") 'Increment the row to the next one currentRow = currentRow + 1 Next jsonRow