VBA, array of hashes to array

Question

I have a json request from an api in excel and I would like to convert it.

This is what I get:

{data:[{price: 3, amount:7},{price: 21, amount:16},{price: 18, amount:4}]}

I would like to convert this into:

{data:[[3,7],[21,16],[18,4]]}

How can I handle this ?

What I'm doing is here sorry about that:

text = DownloadTextFile("www../api/etc")
Set json = JsonConverter.ParseJson(text)

Then I would like to loop through;

For j = 0 To 100
Range("h" & j) = json(data)(j)(1)
Next j

But Range("h" & j) = json(data)(j)(1) of course does not work, as it is not an array. That is why I would like to convert it. I don't know how can I loop through the hash and get price and amount data.

I will use the library and write here in case I encounter with a problem, thank you!

The library most-frequently recommended here is this: github.com/VBA-tools/VBA-JSON. Try that, and post back with code if you run into problems. — Tim Williams
– Tim Williams, Commented Jan 12, 2018 at 19:56

Tim Williams · Accepted Answer · 2018-01-12 20:32:38Z

1

Sub Tester()

    Dim newJ, col, col2
    Dim j, o

    Set j = JsonConverter.ParseJson( _
       "{'data':[{'price': 3, 'amount':7},{'price': 21, 'amount':16},{'price': 18, 'amount':4}]}")

    Set col = New Collection

    For Each o In j("data")
        Set col2 = New Collection
        col2.Add o("price")
        col2.Add o("amount")
        col.Add col2
    Next o

    Set newJ = CreateObject("scripting.dictionary")
    newJ.Add "data", col

    Debug.Print JsonConverter.ConvertToJson(newJ)
    '>> {"data":[[3,7],[21,16],[18,4]]}

End Sub

answered Jan 12, 2018 at 20:32

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

Collectives™ on Stack Overflow

VBA, array of hashes to array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related