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).

Answer 1

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 

Answer 2

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)