如何在没有外部库的情况下使用 VBA 解析 JSON？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19360440/
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
How to parse JSON with VBA without external libraries?
提问by LA_
I have a json like below:
我有一个像下面这样的json：
{"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 arrcontains values like below (checked at Watches):
结果arr包含如下值（在 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.srcworks well, but how can I get arr.sentences(0).trans? Firstly, VBA replaces sentenceswith Sentences, secondly (when I've tried to change the json manually) it still doesn't allow to use sentenses(0).
arr.src效果很好，但我怎样才能得到arr.sentences(0).trans？首先，VBA 替换sentences为Sentences，其次（当我尝试手动更改 json 时）它仍然不允许使用sentenses(0).
回答by July.Tech
I've found this script example useful (from http://www.mrexcel.com/forum/excel-questions/898899-json-api-excel.html#post4332075):
我发现这个脚本示例很有用（来自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
回答by Phil
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.
叫我简单，但我只是声明了一个 Variant 并在每个项目之间的引号逗号引号上拆分来自我的 REST GET 的响应文本，然后通过使用 InStrRev 查找最后一个引号来获得我想要的值。我确信这不像其他一些建议那么优雅，但它对我有用。
varLines = Split(.responsetext, """,""")
strType = Mid(varLines(8), InStrRev(varLines(8), """") + 1)
