gpt4 book ai didi

json - 如何在不使用外部库的情况下使用VBA解析JSON?

转载 作者:行者123 更新时间:2023-12-03 13:34:12 26 4
gpt4 key购买 nike

我有一个像下面的json:

{"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上选中):
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.src效果很好,但是如何获取 arr.sentences(0).trans呢?首先,VBA用 sentences替换了 Sentences;其次(当我尝试手动更改json时),它仍然不允许使用 sentenses(0)

最佳答案

我发现这个脚本示例很有用(来自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

关于json - 如何在不使用外部库的情况下使用VBA解析JSON?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19360440/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com