在Excel VBA中解析JSON对象数组 [英] Parsing a JSON object array in Excel VBA

问题描述

我知道一个类似的问题已经被要求和回答了几次：
在Excel VBA中解析JSON ，
Excel VBA：解析的JSON对象循环

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

但是，如果我尝试访问返回的对象中的数组，则上述解决方案不起作用。我以下列格式从Google Translate API接收JSON对象：

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

}

我想要将两个翻译句子作为句子（0）和句子（1）进行访问。我可以使用之前的帖子中的GetProperty（）方法来检索句子对象，但是我无法访问其成员，因为它是一个类型为JScriptTypeInfo的对象，而不是数组。

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.

我已经尝试使用类似于这里描述的方法将句子对象转换为JScript中的数组：如何在javaScript和VBA之间传递数组。由于某种原因，我只能得到它返回数组的第一个值。

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?

推荐答案

您可以使用 ScriptControl 对象创建一个可以运行javascript的环境。如果您习惯于在网页中使用JSON，那么这可能是一个简单的方法。

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.

示例：

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

如何使用脚本控制调用函数： http：// support .microsoft.com / kb / 184740

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

我们ScriptControl： https://msdn.microsoft。 com / en-us / library / aa227633（v = vs60）.aspx

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

这篇关于在Excel VBA中解析JSON对象数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！