Loop JSON in array for same value in VBA

Question

I am trying to get "image_id" from the below json in different columns of a row.

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

I tried Cannot iterate when parsing HTML table using JSON-VBA and 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

I need "dvuewrnauxdroqcapjiu" in cell B2, tcku7lwarkv8ch0ao9cu in cell C2, and "b2znlmm59plprrkmkujs" in cell C2, but my code is giving no output, no error.

Probably somewhere an error happens. Remove the `On Error Resume Next`, let the code run again and check if any error is throws, in which line it occurs and what it says. — FunThomas, Jul 04 '19 at 12:57

QHarr · Accepted Answer · 2019-07-04T18:50:07.157

3

A number of things.

Your json is malformed. There is an extra "," here:

"Everest Kutilal Coarse Ground Red Chilli Powder ",

which means jsonconverter will throw an error. The "," at the end would be to separate the current key-value pair from the next. There isn't a following pair so it should be removed.

Your path for accessing is wrong.

Here I am reading the corrected json from cell A1

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) will expect B to be a variable as string literals are wrapped in "" i.e. "B". Also, you need a counter variable which is incremented or you will keep writing to the same cell.

edited Jul 04 '19 at 18:50

answered Jul 04 '19 at 18:43

QHarr

83,427
12
54
101

Great. Is there a link of how to read the JSON files and detect if the JSON is correct or not? – YasserKhalil Jul 04 '19 at 19:33
1

Most json viewers will do this for you in terms of validation but you can also use specific validators e.g. https://codebeautify.org/jsonvalidator – QHarr Jul 04 '19 at 20:09
thanks for the answer and link to validate json. I checked my json over https://codebeautify.org/jsonvalidator, its shows "Valid JSON". however, the code you provided did help me.. the correct code : Set json = JsonConverter.ParseJson(A1)(1)(["images"]) Thanks a lot :) – Sweta Garg Jul 05 '19 at 05:50
Hi, then your json is not the same as you posted in the question. If you paste the json above it will error. But glad we got there :-) – QHarr Jul 05 '19 at 05:52
1

Yes, I just checked.. Sorry... I could not share the original so i made some changes manually, i would have done some mistake then.. But anyways, u did solve my problem.. Thanks again. :) – Sweta Garg Jul 05 '19 at 05:53

Loop JSON in array for same value in VBA

1 Answers1