本文介绍了如何在没有外部库的情况下使用 VBA 解析 JSON?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个像下面这样的json:
I have a json like below:
{"sentences":[{"trans":"something ru","orig":"english word","translit":"Angliyskoye slovo","src_translit":""}], "src":"en","server_time":69}并解析它:
Function jsonDecode(jsonString As Variant) Set sc = CreateObject("ScriptControl"): sc.Language = "JScript" Set jsonDecode = sc.Eval("(" + jsonString + ")") End Function Set arr = jsonDecode(txt)结果 arr 包含如下值(在 Watches 中检查):
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 - srcarr.src 运行良好,但我怎样才能获得 arr.sentences(0).trans?首先,VBA 将 sentences 替换为 Sentences,其次(当我尝试手动更改 json 时)它仍然不允许使用 sentences(0).
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).
推荐答案我发现这个脚本示例很有用(来自 www.mrexcel/forum/excel-questions/898899-json-api-excel.html#post4332075 ):
I've found this script example useful (from www.mrexcel/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", "www.omdbapi/?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更多推荐
如何在没有外部库的情况下使用 VBA 解析 JSON?
发布评论