如何在VBA中解析此JSON列表中的数组对象? [英] How can I parse the array objects inside this JSON list in VBA?
问题描述
我的JSON是:
{"totalCount":431,"messages":[],"results":[{"aliasList":["User Id","Name","last name"],"results":[[71512,"joe","adams"],[23445,"jack","wilson"],[34566,jill,goodman]],"executionDate":151134568428}],"Class":"com.zoho.controlpanel.reports.ReportsItemVO"}
我想解析对象,例如在第二个results
键中的[71512,"joe","adams"]
.
I want to parse the objects e.g. [71512,"joe","adams"]
inside the second results
key.
这是我尝试调用JSON-VBA解析器的方法:
And this is my attempt to call the JSON-VBA parser:
Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://controlpanel.zoho.verio/rest/reports/search/reports-call-center-my-assignments", False
http.send
Set JSON = ParseJson(ParseJson(http.responseText)("results"))("results")
Debug.Print JSON
i = 2
For Each Item In JSON
Sheets(5).Cells(i, 1).Value = Item("1")
Sheets(5).Cells(i, 2).Value = Item("2")
Sheets(5).Cells(i, 3).Value = Item("3")
i = i + 1
Next
MsgBox ("complete")
End Sub
我遇到错误
运行时错误450参数数目错误
run-time error 450 wrong number of arguments
我应该在解析器中进行哪些修改,以将这些对象正确解析为电子表格?
What should I modify in my parser to properly parse these objects into a spreadsheet?
推荐答案
我已运行以下代码,该代码已更正且运行正常.我无法打开URL,但已将您给定的jason字符串粘贴到单元格"P1"中.吉尔·古德曼(Jill,goodman)人工将双引号括起来,因为这是错误的.我已经注释了代码的某些部分,您可以在需要时使用它们.
I have run the following code which is corrected and it is working fine. I was unable to open the URL but I have paste the jason string given by you in cell "P1". jill,goodman was manually covered in double quotes as it was wrong. I have commented certain part of code which you can use whenever it is required.
Public Sub exceljson()
Dim http As Object, JSON As Object, Item As Variant
Dim i As Integer
'Set http = CreateObject("MSXML2.XMLHTTP")
jsnStr = Range("P1")
'http.Open "GET", "http://controlpanel.zoho.verio/rest/reports/search/reports-call-center-my-assignments", False
'http.send
'Debug.Print http.responseText
'Set JSON = ParseJson(http.responseText)
Set JSON = ParseJson(jsnStr)
'Fetching data
i = 2
For Each Item In JSON("results")(1)("results")
Sheets(2).Cells(i, 1).Value = Item(1)
Sheets(2).Cells(i, 2).Value = Item(2)
Sheets(2).Cells(i, 3).Value = Item(3)
i = i + 1
Next
Set JSON = Nothing
Set http = Nothing
结束子
这篇关于如何在VBA中解析此JSON列表中的数组对象?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!