How to parse JSON with VBA without external libraries?

Question

I have a json like below:

{"sentences":[{"trans":"something ru","orig":"english word","translit":"Angliyskoye slovo","src_translit":""}], "src":"en","server_time":69}

and parse it:

Function jsonDecode(jsonString As Variant)
    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
    Set jsonDecode = sc.Eval("(" + jsonString + ")")
End Function

Set arr = jsonDecode(txt)

In result arr contains values like below (checked at Watches):

arr
 - sentences (type: Variant/Object/JScriptTypeInfo)
  - 0 (type: Variant/Object/JScriptTypeInfo)
    - orig (type: Variant/String)
    - trans (type: Variant/String)
    ...
  - Item 1 (type: Variant/Object/JScriptTypeInfo)
    - orig (type: Variant/String)
    - trans (type: Variant/String)
    ...
 - server_time
 - src

arr.src works well, but how can I get arr.sentences(0).trans? Firstly, VBA replaces sentences with Sentences, secondly (when I've tried to change the json manually) it still doesn't allow to use sentenses(0).

possible duplicate of [Parsing JSON in Excel VBA](http://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba) Look at [first answer](http://stackoverflow.com/a/7300926/1316573) — Daniel, Oct 14 '13 at 12:48
[Parsing JSON using VBA with **sample file**](http://stackoverflow.com/questions/16817545/handle-json-object-in-xmlhttp-response-in-excel-vba-code/16851758#16851758) — Santosh, Oct 14 '13 at 12:58
@DanielCook, my Excel 2010 doesn't recognize ScriptControl from that answer. — LA_, Oct 15 '13 at 05:10
Right, instead of using `Private ScriptEngine As ScriptControl` put `Private ScriptEngine as Object` and instead of `Set ScriptEngine = New ScriptControl` put `Set ScriptEngine = CreateObject("ScriptControl")` otherwise add the reference indicated in the notes in the answer. This is simply the difference between early and late binding. Either add the reference or update so you don't need it. — Daniel, Oct 15 '13 at 12:42
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, Oct 26 '15 at 20:00
Check [this](http://stackoverflow.com/a/38134477/2165759) to make to work `ScriptControl` on 64-bit Office. — omegastripes, Mar 15 '17 at 14:40
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 si — cyboashu, Nov 13 '14 at 06:36

July.Tech · Answer 1 · 2018-07-11T15:36:23.053

I've found this script example useful (from http://www.mrexcel.com/forum/excel-questions/898899-json-api-excel.html#post4332075 ):

Sub getData()

    Dim Movie As Object
    Dim scriptControl As Object

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://www.omdbapi.com/?t=frozen&y=&plot=short&r=json", False
        .send
        Set Movie = scriptControl.Eval("(" + .responsetext + ")")
        .abort
        With Sheets(2)
            .Cells(1, 1).Value = Movie.Title
            .Cells(1, 2).Value = Movie.Year
            .Cells(1, 3).Value = Movie.Rated
            .Cells(1, 4).Value = Movie.Released
            .Cells(1, 5).Value = Movie.Runtime
            .Cells(1, 6).Value = Movie.Director
            .Cells(1, 7).Value = Movie.Writer
            .Cells(1, 8).Value = Movie.Actors
            .Cells(1, 9).Value = Movie.Plot
            .Cells(1, 10).Value = Movie.Language
            .Cells(1, 11).Value = Movie.Country
            .Cells(1, 12).Value = Movie.imdbRating
        End With
    End With

End Sub

The solution is simple but I see that as potentially dangerous because the parser is not a JSON parser but a JScript parser and malicious code could be injected in the JSON. If you trust your JSON source and still want to use this technique, at least add ScriptControl.UseSafeSubset = True. — Regis Desrosiers, Dec 20 '21 at 17:31

score 5 · Answer 2 · answered Jul 05 '19 at 18:26

5

Call me simple but I just declared a Variant and split the responsetext from my REST GET on the quote comma quote between each item, then got the value I wanted by looking for the last quote with InStrRev. I'm sure that's not as elegant as some of the other suggestions but it works for me.

         varLines = Split(.responsetext, """,""")
        strType = Mid(varLines(8), InStrRev(varLines(8), """") + 1)

answered Jul 05 '19 at 18:26

Phil

61
1
6

Very Simple and I love it. I used 2 or 3 times to get the data I wanted from the JSON. – Andy Jul 29 '21 at 16:33
Quite simple and useful. Thanks¡ – Jam Oct 26 '21 at 15:50

score 1 · Answer 3 · answered Oct 19 '20 at 14:27

There are two issues here. The first is to access fields in the array returned by your JSON parse, the second is to rename collections/fields (like sentences) away from VBA reserved names.

Let's address the second concern first. You were on the right track. First, replace all instances of sentences with jsentences If text within your JSON also contains the word sentences, then figure out a way to make the replacement unique, such as using "sentences":[ as the search string. You can use the VBA Replace method to do this.

Once that's done, so VBA will stop renaming sentences to Sentences, it's just a matter of accessing the array like so:

'first, declare the variables you need:
Dim jsent as Variant

'Get arr all setup, then
For Each jsent in arr.jsentences
  MsgBox(jsent.orig)
Next

Third, JSON keywords are by definition case-sensitive, while VBA object isn't. Set Movie = scriptControl.Eval("(" + .responsetext + ")") will not work if some keys overlap — drgs, Apr 03 '21 at 16:36

How to parse JSON with VBA without external libraries?

3 Answers3

Linked

Related