Totallama Totallama - 1 month ago 20
JSON Question

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

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

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