json - 在数组中循环 JSON 以获得 VBA 中的相同值

Question

我正在尝试从行的不同列中的以下 json 中获取“image_id”。

[
{
"spin": "HM4C6L",
"attributes": {
"product name": "Everest Kutilal Coarse Ground Red Chilli Powder ",
},
"bar_code": {
"valid": true,
"id": "89017817",
"type": "UPC"
},
"spin_state": "LIVE",
"meta": {
"updated-by": "undefined"
},
"version": null,
"images": [
{
"image_id": "dvuewrnauxdroqcapjiu",
"image_name": "masala and spice_HM4A7I2C6L_MN.JPG",
"shot_type": "MN"
},
{
"image_id": "tcku7lwarkv8ch0ao9cu",
"image_name": "masala and spice_HM4A7I2C6L_AL1.JPG",
"shot_type": "AL1"
},
{
"image_id": "b2znlmm59plprrkmkujs",
"image_name": "masala and spice_HM4A7I2C6L_AL2.jpg",
"shot_type": "AL2"
}
]
}
]

我试过Cannot iterate when parsing HTML table using JSON-VBA和 Loop through the JSON object keys in excel vba .

Sub getimage()

Dim current As Workbook
Dim sht As Worksheet
Dim a, b, strUrl As String
Dim count As Variant

Set current = ActiveWorkbook

For Each sht In current.Worksheets

    On Error Resume Next
    'Application.ScreenUpdating = False
    Set HTTPReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    count = Range("A1", Range("A1").End(xlDown)).Rows.count

    For i = 2 To count

        a = CStr(Range("A" & i).Value)
        HTTPReq.Open "GET", "link" & a, False
        HTTPReq.send
        'Debug.Print HTTPReq.ResponseText

        Dim Json, item As Object
        Set Json = JsonConverter.ParseJson(HTTPReq.ResponseText)

        For Each item In Json
            Debug.Print item("images")
            sht.Cells(i, B) = item("image_id")("1")
            sht.Cells(i, B) = item("image_id")("2")    
        next Item

    Next i
    'Application.ScreenUpdating = True

End If
Next sht

End Sub

我需要单元格 B2 中的“dvuewrnauxdroqcapjiu”、单元格 C2 中的 tcku7lwarkv8ch0ao9cu 和单元格 C2 中的“b2znlmm59plprrkmkujs”，但我的代码没有给出任何输出，也没有错误。

Answer 1

很多事情。

1. 您的 json 格式错误。这里多了一个“，”:

"Everest Kutilal Coarse Ground Red Chilli Powder ",

这意味着 jsonconverter 将抛出错误。末尾的“,”用于将当前键值对与下一个键值对分隔开。没有后续对，因此应将其删除。

您的访问路径错误。

这里我正在从单元格 A1 读取更正后的 json

Option Explicit   
Public Sub test()
    Dim json As Object, i As Long, item As Object, c As Long
    i = 2: c = 2
    Set json = JsonConverter.ParseJson([A1])(1)("images")
    For Each item In json
        ActiveSheet.Cells(i, c) = item("image_id")
        c = c + 1
    Next
End Sub

Cells(2,B) 将期望 B 是一个变量，因为字符串文字被包裹在“”中，即“B”。另外，您需要一个递增的计数器变量，否则您将继续写入同一单元格。