0

I'm really at my wit's end here... I'm using VB-JSON Parser (http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html) and I have the following array : 

[{"timestamp":1410001952,"tid":2834225,"price":"483.77"}]

The documentation is really minimal and I have no clue whatsoever of how to access the array, been searching for several hours now on how to resolve this.

How can I get the "price" value? I know that i can use .item("price") when there is no array but I don't know what to do when there's an array and there is no name before it.

Community
  • 1
  • 1
uncool
  • 2,613
  • 7
  • 26
  • 55

2 Answers2

1

First have a look at Parsing JSON in Excel VBA It explains the JScript way of parsing JSON string.

Browsing through the net, I found it really hard to get a complete VBA based JSON parser. Some options are available in the VB version and then there are few online parsers who promise to parse JSON and convert them in Excel. These ones work fine with simple JSON data structure. But once you feed in a complex data set with nested arrays and structures, they simply fail.

Using JavaScript features of parsing JSON, on top of ScriptControl, we can create a parser in VBA which will list each and every data point inside the JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this parser will return a complete tree structure.

JavaScript’s Eval, getKeys and getProperty methods provide building blocks for validating and reading JSON.

Coupled with a recursive function in VBA we can iterate through all the keys (up to nth level) in a JSON string. Then using a Tree control (used in this article) or a dictionary or even on a simple worksheet, we can arrange the JSON data as required.

Here, you can find a complete VBA example.

Community
  • 1
  • 1
cyboashu
  • 10,196
  • 2
  • 27
  • 46
  • Note that the above approach makes the system vulnerable in some cases, since it allows the direct access to the drives (and other stuff) for the malicious JS code via ActiveX's. Let's suppose you are parsing web server response JSON, like `JsonString = "{a:(function(){(new ActiveXObject('Scripting.FileSystemObject')).CreateTextFile('C:\\Test.txt')})()}"‌`​. After evaluating it you'll find new created file `C:\Test.txt`. So JSON parsing with `ScriptControl` ActiveX is not a good idea. Check the [update of my answer](http://stackoverflow.com/a/30494373/2165759) for the RegEx-based JSON parser. – omegastripes Nov 03 '15 at 19:52
0

There is a JSON serializer in .NET: http://msdn.microsoft.com/en-us/library/system.runtime.serialization.json

MichalP
  • 1