将带有数组的JSON转换为Excel宏

My json content is : https://czchols3439.prg-dc.dhl.com:9292/rest/hierarchy/cp/9BFD4668B3EE5F09E053512C48A5B19E

我的json到excel转换VBA代码是:

Public Sub exceljson_Type1()
Dim https As Object, Json As Object, i As Integer
Dim Item As Variant, div As Variant
Set https = CreateObject("MSXML2.XMLHTTP")
https.Open "GET", "https://czchols3439.prg-    dc.dhl.com:9292/rest/hierarchy/cp/9BFD4668B3EE5F09E053512C48A5B19E", False
https.Send
Set Json = JsonConverter.ParseJson(https.responseText)
Set div = JsonConverter.ParseJson(divisions)
i = 3
For Each Item In Json
For Each div In Item
Sheets(1).Cells(i, 1).Value = Item("uuid")
Sheets(1).Cells(i, 2).Value = Item("code") 
Sheets(1).Cells(i, 3).Value = Item("customerSegment")
Sheets(1).Cells(i, 4).Value = Item("marketGroup")
Sheets(1).Cells(i, 5).Value = Item("corporatePartnerType")
Sheets(1).Cells(i, 6).Value = div("uuid")
Sheets(1).Cells(i, 7).Value = div("code")
Sheets(1).Cells(i, 8).Value = div("name")
Sheets(1).Cells(i, 9).Value = div("shortName")
Sheets(1).Cells(i, 10).Value = div("remarks")
Sheets(1).Cells(i, 11).Value = div("uuid")
Sheets(1).Cells(i, 12).Value = div("cw1Code")
Sheets(1).Cells(i, 13).Value = div("name")
Sheets(1).Cells(i, 14).Value = div("uuid")
Sheets(1).Cells(i, 15).Value = Item("addressLine1")
Sheets(1).Cells(i, 16).Value = Item("city")
Sheets(1).Cells(i, 17).Value = Item("zip")
Sheets(1).Cells(i, 18).Value = Item("countryCode")
i = i + 1
Next div
Next Item
MsgBox ("complete")
End Sub

但是低于错误 预期{或[

有人可以帮助我更改代码并获得所需的输出。这是嵌套值,我使用了两个循环。

评论