为什么 VBA-JSON 解析时出错？无法访问正确的节点

Question

**我有与Excel VBA相同的问题:解析JSON对象循环，但找不到任何解决方案**

项目值为"msg"但我需要到达节点recordset;而解析，根据Json文件)我不知道如何做到这一点。就像我在代码中缺少了一些东西来移动json文件

我的Json

{
"msg": "Operation successful.",
"recordset": {
"0": {
"REFX": "140*1*70R",
"TALLER": "70R",
"NOM_TALLER": "MINUTO",
"MARCA_IV": "RE",
"LOCALIDAD": " (VALENCIA)",
"ALMACEN": "70",
"NOM_ALMACEN": "PALACIO ",
"EMPRESA": "70",
"NOM_EMPRESA": "tralari, sa",
"DIRECCION_TALLER": "KM 2.900",
"NOM_COMERCIAL": "MINUTO comercial"
},
"1": {
"REFX": "140*1*71R",
"TALLER": "71R",
"NOM_TALLER": "pUERTO MINUTO",
"MARCA_IV": "RE",
"LOCALIDAD": "VALENCIA",
"ALMACEN": "71",
"NOM_ALMACEN": "ISLAS",
"EMPRESA": "70",
"NOM_EMPRESA": "auto",
"DIRECCION_TALLER": "laralere",
"NOM_COMERCIAL": "ssss"
}
}
}

我的代码

Function QuiterExpl(MyYear As Double, MyMonth As Double) As String
On Error GoTo TrataErrores
Dim http As Object
Dim MyRecordSet As Object
Dim i As Integer
Dim recordCall As Object
Dim recordSet As Variant
Dim Item As Object
Dim blnAsync As Boolean
Dim strUrl As String
Dim ws As Worksheet
Set ws = Worksheets("test")
i = 3
blnAsync = False
strUrl = "https://api.renaultretailgroup.es/superget?tabla=FMTABI_PR"
Set http = CreateObject("MSXML2.XMLHTTP")
With http
.Open "GET", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.SetRequestHeader "Authorization", "Bearer " & "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VybmFtZSI6eyJpZF91c2VyIjoxLCJ1c2VybmFtZSI6InMwMTEzNDgifSwicm9sZXMiOlsxXSwiaWF0IjoxNjI4MTUzNDM4LCJleHAiOjE2NTk3MTEwMzh9.PTBus4_g-Kb34NkQoMEnl1aJHAvTO1ZoUiAGVPprGOM"
.Send
End With
Set MyRecordSet = JsonConverter.ParseJson(http.responseText)
Set recordCall = MyRecordSet("recordset")
For Each recordSet In recordCall
Set Item = recordCall(recordSet)
ws.Cells(i, 4).Value = Item("REFX")
ws.Cells(i, 5).Value = Item("TALLER")
ws.Cells(i, 6).Value = Item("NOM_TALLER")
ws.Cells(i, 7).Value = Item("MARCA_IV")
ws.Cells(i, 8).Value = Item("LOCALIDAD")
ws.Cells(i, 9).Value = Item("ALMACEN")
ws.Cells(i, 10).Value = Item("NOM_ALMACEN")
ws.Cells(i, 11).Value = Item("EMPRESA")
i = i + 1
Next
MsgBox ("Carga finalizada")
TrataErrores:
MsgBox Err.Description & "-" & Err.Number

End Function

Answer 1

您的代码仅循环通过MyRecordSet-msg和recordset中的2项。要访问您想要的内容，您可以尝试这样做:

... Your XMLHTTP Request code...
Set MyRecordSet = JsonConverter.ParseJson(http.responseText)
Dim i As Long
i = 3

Dim recordColl As Object
Set recordColl = MyRecordSet("recordset")

Dim recordSet As Variant
For Each recordSet In recordColl
Dim Item As Object
Set Item = recordColl(recordSet)
Sheets(1).Cells(i, 1).Value = Item("REFX")  '** ERROR
Sheets(1).Cells(i, 2).Value = Item("TALLER")
Sheets(1).Cells(i, 3).Value = Item("NOM_TALLER")
Sheets(1).Cells(i, 4).Value = Item("MARCA_IV")
Sheets(1).Cells(i, 5).Value = Item("LOCALIDAD")
Sheets(1).Cells(i, 6).Value = Item("ALMACEN")
Sheets(1).Cells(i, 7).Value = Item("NOM_ALMACEN")
Sheets(1).Cells(i, 8).Value = Item("EMPRESA")
i = i + 1
Next recordSet

注意:声明所有变量是很好的做法，请在模块的顶部添加Option Explicit以帮助您执行它。