如何使用vba获取json数组中的数据？
如何使用vba获取json数组中的数据？
提问于 2019-06-24 22:06:32
如何执行循环以从项目_data中获取数据，如代码、company_name等？
{"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“，但返回值为空。您能帮助我吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-26 08:33:26
这不是一个实际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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56738352
复制相关文章
相似问题