Using JavaScript scripting language to parse JSON data in VBA

Json:javascript Object Notation (JAvaScript Object Notation)

Similar to XML, in the form of text (saved in a text file or a string, for example), such as:

JSSTR = {"Department": "History department", "Class": "Class", "           student":[                   {"name": "Zhang San", "Age": 25, "gender": "Male"},                   {"name": " John Doe "," age ": 20," gender ":" Male "},                   {" name ":" Xiaoming "," Age ": 20," gender ":" Female "}                  ]          }        

1. {} represents an object, [] represents an array, the member of an array can be an object, and a property is represented by a property: Value key value pair.

In this example, the Jsstr object has three properties: Department, class, student. Where the value of the learner attribute is an array of three learner objects,

2, the relative XML is lighter, the XML tag is more than the data.

In VBA, you can use JavaScript to parse JSON objects:

Refer to Microsoft Script Control 1.0 First, file is: MSScript.ocx

1 SubTestjson ()2 DimJsstr as String3 4 'vb to make a string to add n quotation marks, really annoying5Jsstr ="{"" "" " " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" """& _6          ""& _7                    "{"Name" ":" "John Doe" "," "Age" ": 20," "Gender" ":" "Male" "},"& _8                    "{"Name" ":" "Xiaoming" "," "Age" ": 20," "Gender" ":" "Female" "}"& _9                   "]"& _Ten          "}" One          A 'early binding easy to use smart tips - DimScobj as NewMsscriptcontrol.scriptcontrol - DimAge as Integer the  - 'the scripting language used by ScriptControl. In addition to JS, VBScript is also supported -Scobj. Language ="JavaScript" -  + 'Add code to the script, the argument is a string -Scobj. Addcode ("var query ="&jsstr) +  A 'A JSON object represents a member that can be used: an object. Properties at 'Property if it is an array that contains multiple objects, you can use an index representation: object. property [0] - 'Eval is an expression evaluation -Age = Scobj. Eval ("query. Student [2]. Age") -  -Debug.Print"The age of the 3rd student in the history department is"& Age &"years" -          in End Sub

1. JavaScript is a weakly typed language. The keyword that defines the variable is var

var x; var y = 10; var name = "Zhang San";

2, Eval is the expression evaluation, the argument is a string, if it is to parse a JSON object, it is best to use a pair of parentheses to enclose the text, so as to avoid grammatical errors.

var obj = eval ("(" + Jsstr + ")");

3, CodeObject method, exposing JSON object to VB. (This method is not to be figured out)

1 SubTestJson2 ()2 DimJsstr1 as String3 DimJsstr2 as String4JSSTR1 ="{"Name" ":" "Zhang San" "," "Gender" ":" "Male" "}"5JSSTR2 ="{"Name" ":" "Floret" "," "Gender" ":" "Female" "}"6 7 DimScobj as NewMsscriptcontrol.scriptcontrol8Scobj. Language ="JavaScript"9Scobj. Addcode ("var query1 ="& JSSTR1)'Query1,query2 are two JavaScript objects, respectively .TenScobj. Addcode ("var query2 ="&jsstr2)one ' callbyname remove the Query1, Query2 object from CodeObject, and find the value of the name attribute
 ADebug.PrintCallByName(CallByName(Scobj. CodeObject,"Query1", Vbget),"name", Vbget) -Debug.PrintCallByName(CallByName(Scobj. CodeObject,"Query2", Vbget),"name", Vbget) -  the End Sub

CallByName is the method in VB.

The local view allows you to see the Scobj object and the internal CodeObject

Using JavaScript scripting language to parse JSON data in VBA