4

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.

Community
  • 1
  • 1
Abiel
  • 5,251
  • 9
  • 54
  • 74
  • This has a security risk, see this blog post http://exceldevelopmentplatform.blogspot.com/2018/01/vba-parse-json-safer-with-jsonparse-and.html The same blog post directs to using Douglas Crockford's parsing library which solves security hole. – S Meaden Jan 21 '18 at 13:00

2 Answers2

0

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

cyboashu
  • 10,196
  • 2
  • 27
  • 46
-1

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

Community
  • 1
  • 1
ozmike
  • 2,738
  • 1
  • 33
  • 40