Use ScriptControl to parse JSON in VBA: transform result to dictionaries and collections

Question

I would like to use Microsoft ScriptControl to parse a JSON string in VBA, and then transform the resulting Object into Dictionary and Collection objects. I already know how to do the parsing with ScriptControl, but cannot figure out how to map the result into the Dictionary and Collection classes. I'm guessing that if I could figure out how to loop through the properties of an Object this would become clear...

Dim sc As ScriptControl
Dim obj As Variant

Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"

Set obj = sc.Eval("("+json+")") ' json is a string containing raw JSON

' Now what?

By the way, I've used the vba-json library to get the output in terms of Dictionaries and Collections, but I find this library somewhat slow. It does not use ScriptControl.

EDIT: I found a discussion of getting object properties in this post.

Answer 1

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.

VBA Code:http://ashuvba.blogspot.in/2014/09/json-parser-in-vba-browsing-through-net.html

Answer 2

loop This will help you to loop - add a myitem(n) method in javascript from there you can map through VB code.