如何在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屋！