Parsing a JSON object array in Excel VBA

Question

I know a similar question has been asked and answered before a few times: Parsing JSON in Excel VBA, Excel VBA: Parsed JSON Object Loop

However, the above solution doesn't work if I am trying to access an array within the returned object. I'm receiving a JSON object from the Google Translate API in the following format:

"{
"sentences":[
    {
        "trans":"Responsibility\n",
        "orig":"??",
        "translit":"",
        "src_translit":"Zérèn"
    },
    {
        "trans":"Department",
        "orig":"??",
        "translit":"",
        "src_translit":"Bùmén"
    }
],
"src":"zh-CN",
"server_time":86

}"

I want to be able to access the two translated sentences as sentences(0) and sentences(1). I can use the GetProperty() method from the previous posts to retrieve the sentences object, but I can't access its members because it is an object of type JScriptTypeInfo, not an array.

I've tried to convert the sentences object to an array in JScript using something similar to the method described here: How to pass arrays between javaScript and VBA. I can only get it to return the first value of the array, for some reason.

What would be the best way to do this?

Answer 1

You can use the ScriptControl object to create an environment where you can run javascript. If you're used to working with JSON in web pages then this can be an easy way to go.

Example:

Sub Tester()

    Dim json As String
    Dim sc As Object
    Dim o

    Set sc = CreateObject("scriptcontrol")
    sc.Language = "JScript"

    json = {get your json here}

    sc.Eval "var obj=(" & json & ")" 'evaluate the json response
    'add some accessor functions
    sc.AddCode "function getSentenceCount(){return obj.sentences.length;}"
    sc.AddCode "function getSentence(i){return obj.sentences[i];}"

    Debug.Print sc.Run("getSentenceCount")

    Set o = sc.Run("getSentence", 0)
    Debug.Print o.trans, o.orig
End Sub

How To Call Functions Using the Script Control : http://support.microsoft.com/kb/184740

Using the ScriptControl: https://msdn.microsoft.com/en-us/library/aa227633(v=vs.60).aspx