在 Excel VBA 中解析 JSON 对象数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14822672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parsing a JSON object array in Excel VBA
提问by user2062989
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
我知道之前有人问过并回答过类似的问题： 在 Excel VBA 中解析 JSON， 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:
但是，如果我尝试访问返回对象中的数组，则上述解决方案不起作用。我从 Google Translate API 接收到以下格式的 JSON 对象：
"{
"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.
我希望能够访问两个翻译的句子作为句子（0）和句子（1）。我可以使用之前帖子中的 GetProperty() 方法来检索句子对象，但我无法访问其成员，因为它是 JScriptTypeInfo 类型的对象，而不是数组。
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.
我尝试使用类似于此处描述的方法将句子对象转换为 JScript 中的数组：如何在 javaScript 和 VBA 之间传递数组。由于某种原因，我只能让它返回数组的第一个值。
What would be the best way to do this?
什么是最好的方法来做到这一点？
回答by Tim Williams
You can use the ScriptControlobject 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.
您可以使用该ScriptControl对象创建一个可以运行 javascript 的环境。如果您习惯于在网页中使用 JSON，那么这可能是一种简单的方法。
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
如何使用脚本控件调用函数：http: //support.microsoft.com/kb/184740
Using the ScriptControl: https://msdn.microsoft.com/en-us/library/aa227633(v=vs.60).aspx
使用 ScriptControl：https://msdn.microsoft.com/en-us/library/aa227633( v=vs.60).aspx
