25

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

Improve this question
8
  • 2
    possible duplicate of Parsing JSON in Excel VBA Look at first answer Commented Oct 14, 2013 at 12:48
  • Parsing JSON using VBA with sample file Commented Oct 14, 2013 at 12:58
  • 2
    @DanielCook, my Excel 2010 doesn't recognize ScriptControl from that answer. Commented Oct 15, 2013 at 5: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. Commented Oct 15, 2013 at 12:42
  • 1
    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 for the RegEx-based JSON parser. Commented Oct 26, 2015 at 20:00

3 Answers 3

Reset to default
21

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I get "ActiveX component can't create object."
You may need to reference MSXML2.XMLHTTP60 instead.
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.
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)
Improve this answer

1 Comment

Very Simple and I love it. I used 2 or 3 times to get the data I wanted from the JSON.
1

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
Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.