CODEWITHSUNDEEP
jsonvbawinhttpwinhttprequestvba6
heisenberg
heisenberg

Reputation: 1954

VBA How To Loop Through JSON response from WinHttp.WinHttpRequest

I can't figure out how to properly loop through a JSON(Object) response from the WinHttp.WinHttpRequest that I am getting.

Below are the References being used. I prefer to just keep it as is and use WinHttpRequest

enter image description here

Dim response As Object ' global


Function sendRequest(requestURL As String) ' send the http REST request url of API transaction
    Dim request As New WinHttp.WinHttpRequest
    request.Open "GET", requestURL, True
    request.setRequestHeader "Authorization", "Bearer " + tokenResp
    request.setRequestHeader "Accept", "application/json"
    request.send
    request.waitForResponse
    
    
    Set response = ParseJson(request.ResponseText)
    

    ' Debug.Print vbNewLine & "Response : " & vbNewLine
    ' Debug.Print "Request ResponseText : " & request.ResponseText
End Function

Below is how the JSON response I am getting looks like. There are more records.

{
   "Record":[
      {
         "NameValue":[
            {
               "Name":"name1",
               "Value":"value1"
            },
            {
               "Name":"name2",
               "Value":"value2"
            }
         ]
      },
      
      {
         "NameValue":[
            {
               "Name":"name1",
               "Value":"value1"
            },
            {
               "Name":"name2",
               "Value":"value2"
            }
         ]
      }
   ]
}

The response is an object. I can do Debug.Print response("Record")(1)("NameValue")(1)("Value") to get the first record

Debug.Print response("Record")(1)("NameValue")(1)("Value") ' value1

but I need to be able to loop through it to get all values and not just the first one

I can't seem to find a way to convert the JSON to an array with a array length() or size() function. I searched and found UBound() and LBound() but I think it can only be used on arrays and not Objects.

I'd appreciate any help. I do Java most of the times and things are a bit different in VBA.

Thank you.

Upvotes: 1

Views: 1687

Answers (1)

Tim Williams
Tim Williams

Reputation: 166391

response("record") is a Collection (also anything else in [] in your json). Each item in that collection is a Dictionary (likewise anything in {})

Dim itmRec, nameVal, itm

For each itmRec in response("Record")
    set nameVal = itmRec("NameValue")
    for each itm in nameVal
        debug.print itm("Name"), itm("Value")
    next itm
Next itmRec

Upvotes: 1

Related Questions