Parse JSON with VBA

[kanvian]

Hi everybody,

I would like to parse a json string in VBA using this library :
VB JSON Parser Library for VB6 (BSD Licensed) - Visual Basic, JSON

here is an example of the json string I receive from a Web Service and want to parse:


{
"total": [[10000,1]],
"periods": [1399852800000],
"series": {"A": [[102,20]],"B": [[13,25]],"C": [[189,55]]},
"meta": {
"versions": {"selected": ["all"],"available": ["all","2.0","1.0","unknown"]},
"series": ["A","B","C",],
"time-zone": "Coordinated Universal Time",
"alternative-exports": ["csv"],
"period": "2014-05-12:2014-05-18",
}
}

here is the code I wrote in VBA (jsonText is the json string above):

Dim jsonObj As Dictionary​

Set jsonObj = json.parse(jsonText)​

Unfortunately it does not work, the dictionary I obtain (jsonObj) seems to contain only 2 keys (total and periods). Therefore I cannot build this table in excel:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]13[/TD]
[TD]189[/TD]
[/TR]
</tbody>[/TABLE]


Does anyone have any ideas to solve this problem ?
Thank you very much for reading and help.

 

[peterennis]

Late to this, but paste the code to jslint.com and it errors because
"series": {"A": [[102,20]],"B": [[13,25]],"C": [[189,55]]}, has [[ and ]]
and also with
"series": ["A","B","C",], is wrong - there should be not , before ]
and
"period": "2014-05-12:2014-05-18", has a trailing ,

This validates but I am not sure if it is what you expect:
{
"total": [
[
10000,
1
]
],
"periods": [
1399852800000
],
"series": {
"A": [
102,
20
],
"B": [
13,
25
],
"C": [
189,
55
]
},
"meta": {
"versions": {
"selected": [
"all"
],
"available": [
"all",
"2.0",
"1.0",
"unknown"
]
},
"series": [
"A",
"B",
"C"
],
"time-zone": "Coordinated Universal Time",
"alternative-exports": [
"csv"
],
"period": "2014-05-12:2014-05-18"
}
}