json - 如何使用 vba 获取 json 数组中的数据?
问题描述
如何执行循环以从项目 _data 中获取数据,例如代码、公司名称、...?
JSON 看起来像:
{"ERR":"0",
"error_code":"",
"error_message":"",
"projects_data":{"173":
{"id":"173",
"code":"PRJ-0163",
"company_id":"27",
"company_name":"Associa\u00e7\u00e3o - Interven\u00e7\u00e3o de excel\u00eancia no sector Agro-Alimentar",
"company_logo":"",
"name":"PROJECT NAME",
"reference":"20190127",
"description":"France,Germany",
"allowoutsiders":"1",
"barcode":"",
"stages_data":{"648":
...
<
ERR: "0", error_code: "", error_message: "",…}
ERR: "0"
error_code: ""
error_message: ""
projects_data: {25: {id: "25", code: "PRJ-0015", company_id: "10",…},…}
25: {id: "25", code: "PRJ-0015", company_id: "10",…}
34: {id: "34", code: "PRJ-0024", company_id: "15", company_name: "Ceu - Indústria e Comércio, S.A.",…}
44: {id: "44", code: "PRJ-0034", company_id: "3", company_name: "Roca, Turismo e Indústria S.A.",…}
49: {id: "49", code: "PRJ-0039", company_id: "33",…}
50: {id: "50", code: "PRJ-0040", company_id: "33",…}
51: {id: "51", code: "PRJ-0041", company_id: "33",…}
52: {id: "52", code: "PRJ-0042", company_id: "33",…}
67: {id: "67", code: "PRJ-0057", company_id: "3", company_name: "Roca, Turismo e Indústria S.A.",…}
77: {id: "77", code: "PRJ-0067", company_id: "235", company_name: "Paper UK Ltd", company_logo: "",…}
78: {id: "78", code: "PRJ-0068", company_id: "234",…}
80: {id: "80", code: "PRJ-0070", company_id: "238", company_name: "Azeitona...
>
Sub Macro1()
Dim jsonText As String
Dim JsonObject As Object, Item As Object
Set hreq2 = CreateObject("MSXML2.XMLHTTP")
With hreq2
.Open "GET", "https://192.111.0.xxx/ws/mobile/index.php?method=getProjects&SESSION_KEY=SOAPP5d10b8a06bc6f5.xxxx&action=all"
.Send
End With
Dim RESPONSE2 As String
RESPONSE2 = "[" & hreq2.ResponseText & "]"
Range("a1").Value = RESPONSE2
Set JsonObject = JsonConverter.ParseJson(RESPONSE2)
i = 1
Dim keyCurr As String
For Each Item In JsonObject
ActiveSheet.Cells(i + 2, 2).Value = Item.Keys
For Each curr In Item
If curr = "projects_data" Then
Range("A8").Value = JsonObject(i)(curr)("code")
Range("A9").Value = JsonObject(i)(curr)("company_id")
i = i + 1
Else
End If
Next curr
Next
End Sub
“我进入了“projects_data”,但返回是空的。你能帮帮我吗?
解决方案
对于您的实际 JSON 来说,这并不是一个足够大的示例,但例如:
Dim JsonObject As Object, projects As Object, project As Object, k, sk, v, stages
Set JsonObject = JsonConverter.ParseJson(hreq2.ResponseText) 'don't add []
Set projects = JsonObject("projects_data") 'projects is a Dictionary object
For Each k In projects.keys
Debug.Print "----------Project " & k
Set project = projects(k) 'another Dictionary
'loop over some of the dictionary keys
For Each v In Array("id", "code", "name", "reference")
Debug.Print v, project(v)
Next v
Set stages = project("stages_data")
for each sk in stages.keys
debug.print "stage", sk, stages(sk)
next sk
Next k
推荐阅读
- javascript - 如何防止第一次重新渲染 Formik
- javascript - 仅在字典位置的数组值总和 - Javascript
- node.js - 是否可以让员工在 Heroku 中“虚拟”24 小时/7 小时运行?
- node.js - sequelize ERROR: this.lib.createConnection is not a function when using node
- python - 如何使用 python 从 NBA 网站上抓取统计数据
- c++ - makefile 文件名参数
- swift - 单个 UIView 和多个 TableView 的自动布局
- java - StringBuilder - 附加 2 个字符或 1 个字符串
- wordpress - 将 Javascript API 用于高级自定义字段
- javascript - 输入选项列表