使用JSON-VBA解析HTML表时无法迭代 [英] Cannot iterate when parsing HTML table using JSON-VBA

问题描述

这是我要解析的用户表的JSON视图中的示例"，其中包括aliasList作为HTML表中每一列的标题，其余用户位于该表下方.

This is an example in JSON view of my users table that i want to parse", inluding an aliasList as the title for each column in the HTML table and the remaining users are below it.

{"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"}

这是我的解析调用程序，它收到编译错误:

And this my parse caller that is getting a Compile error:

For Each只能迭代集合对象或数组

For Each may only iterate over a collection object or an array

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/NONE&offset=0", False
http.send
Set JSON = ParseJson(http.responseText)
i = 2
Dim data As String
data = JSON("results")(2)
Dim parsedData As Dictionary
Set parsedData = ParseJson(data)
For Each item In data
Sheets(5).Cells(i, 1).Value = item("results")(2) 'key is called simply "2"
Sheets(5).Cells(i, 2).Value = item("results")(4)
Sheets(5).Cells(i, 3).Value = item("results")(6)
Sheets(5).Cells(i, 4).Value = item("results")(13)
Sheets(5).Cells(i, 5).Value = item("results")(16)
Sheets(5).Cells(i, 6).Value = item("results")(18)
i = i + 1
Next
MsgBox ("complete")
End Sub

我尝试了Data声明中的许多组合，以及要解析哪个嵌套数组都没有成功.但是，在这种情况下，我不知道是否需要使用aliasList，我不知道该怎么做.

I tried many combinations in the Data declaration and which nested array to parse without success. However, i don't know if i need to use the aliasList in this case, which i don't know how to do it.

我应该在通话中修改什么?

What should i modify in my call?

推荐答案

您的JSON格式错误，因为每个唯一键只能有一个实例. 解析第二个结果"时，它只会覆盖第一个结果.

Your JSON is malformed, since you can only have one instance of each unique key. When the second "results" is parsed it just overwrites the first.

这对我有用(修复了JSON中的错误之后):

This worked for me (after fixing the errors in your JSON):

Dim j, k, o

'loading json from worksheet cell...
Set j = JsonConverter.ParseJson(Sheet1.Range("B6").Value)

For Each o In j("results")
    Debug.Print o("1"), o("2"), o("3")
Next

已针对您的实际" json更新:

updated for your "actual" json:

Sub Tester55()

    Dim j, c, res, v

    'loading json from worksheet cell...
    Set j = JsonConverter.ParseJson(Sheet1.Range("B6").Value)
    Set res = j("results")

    Set c = ActiveSheet.Range("F2")

    WriteCollection c, res(1)("aliasList")

    For Each v In res(1)("results")
        Set c = c.Offset(1, 0)
        WriteCollection c, v
    Next v

End Sub

Sub WriteCollection(rng, col)
    Dim v, i As Long
    For Each v In col
        rng.Offset(0, i).Value = v
        i = i + 1
    Next v
End Sub

这篇关于使用JSON-VBA解析HTML表时无法迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！