Using JavaScript scripting language to parse JSON data in VBA

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.