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..

That's not valid JSON. You're mixing array and object notation. — Robby Cornelissen
– Robby Cornelissen, Commented Nov 7, 2016 at 7:34
Robby Cornelissen: Sorry, I messed that up. Now it is correct. — Totallama
– Totallama, Commented Nov 7, 2016 at 7:40
The one I downloaded here: ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html — Totallama
– Totallama, Commented Nov 7, 2016 at 9:20

Robby Cornelissen · Accepted Answer · 2016-11-07 09:45:08Z

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

edited Nov 7, 2016 at 9:45

answered Nov 7, 2016 at 7:44

Robby Cornelissen

98.6k23 gold badges154 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Robby Cornelissen Over a year ago

And on what line?

Totallama Over a year ago

I got it, this line: ws.Cells(currentRow, startColumn).Value = jsonRow("Id")

Totallama Over a year ago

Yeah, it is in cell A1 in VIEW sheet.

user20416 Over a year ago

@Totallama If I Dim jsonRows As Variant instead of As Collection, then it works.

user20416 Over a year ago

@Totallama I meant Dim jsonRow As Variant; jsonRows works as either a Variant or a Colleciton in this case.

|

Collectives™ on Stack Overflow

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

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related