Access values in nested JSON-Object in VBA

[Ambientolo]

Hello there,

I would like to get data from a JSON-Object, that I got from a Rest-API, with VBA to display some data into an Excel-Worksheet.

I have the following JSON-Object:

{
  "devices": [
    {
      "data": [
        {
          "id": 0,
          "name": "Hello"
        },
    {
          "id": 1,
          "name": "How are you?"
        },
    {
          "id": 2,
          "name": "Bye"
        }
      ],
      "type": "LORA"
    }
  ],
  "includedTypes": [
    "LORA"
  ]
}

I want to get the objects in the array from "data".

My VBA-Code is this:

Dim js1Object As Object
        Dim response1 As String
        strUrl = "https://XXXXXXXXXXXdevices
        Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .SetRequestHeader "Authorization", "Bearer " & apitoken
        .Send
        response1 = hReq.responseText
        MsgBox response1
    Set js1Object = JsonConverter.ParseJson(response1)
    j = 31
        For Each item In js1Object("devices")
        ws.Cells(j, 7) = item("id")
        ws.Cells(j, 10) = item("name")
        j = j + 1
    Next
        MsgBox (response1)
    End With

How can I access the values from "data"?

If the JSON would look like the object below, my code would work. But my problem is, that the response that I get, is more nested and I can't directly access "data".

{
      "devices": [
        {
          "id": 0,
          "name": "Hello"
        },
    {
          "id": 1,
          "name": "How are you?"
        },
    {
          "id": 2,
          "name": "Bye"
        }
      ]
    }

I just don't know, how to access deeper values in JSON-Oject.

Thanks for helping me!

[houghtonap]

See my response in issue #186 for accessing values in a JSON-Object.

Sent from Mailhttps://go.microsoft.com/fwlink/?LinkId=550986 for Windows 10

From: Ambientolomailto:notifications@github.com Sent: Sunday, February 7, 2021 2:25 PM To: VBA-tools/VBA-JSONmailto:VBA-JSON@noreply.github.com Cc: Subscribedmailto:subscribed@noreply.github.com Subject: [VBA-tools/VBA-JSON] Access values in nested JSON-Object in VBA (#191)

Hello there,

I would like to get data from a JSON-Object, that I got from a Rest-API, with VBA to display some data into an Excel-Worksheet.

I have the following JSON-Object:

{

"devices": [

{

  "data": [

    {

      "id": 0,

      "name": "Hello"

    },

    {

      "id": 1,

      "name": "How are you?"

    },

    {

      "id": 2,

      "name": "Bye"

    }

  ],

  "type": "LORA"

}

],

"includedTypes": [

"LORA"

]

}

I want to get the objects in the array from "data".

My VBA-Code is this:

Dim js1Object As Object

    Dim response1 As String

    strUrl = "https://XXXXXXXXXXXdevices

    Set hReq = CreateObject("MSXML2.XMLHTTP")

With hReq

    .Open "GET", strUrl, False

    .SetRequestHeader "Authorization", "Bearer " & apitoken

    .Send

    response1 = hReq.responseText

    MsgBox response1

Set js1Object = JsonConverter.ParseJson(response1)

j = 31

    For Each item In js1Object("devices")

    ws.Cells(j, 7) = item("id")

    ws.Cells(j, 10) = item("name")

    j = j + 1

Next

    MsgBox (response1)

End With

How can I access the values from "data"?

If the JSON would look like the object below, my code would work. But my problem is, that the response that I get, is more nested and I can't directly access "data".

{

  "devices": [

    {

      "id": 0,

      "name": "Hello"

    },

    {

      "id": 1,

      "name": "How are you?"

    },

    {

      "id": 2,

      "name": "Bye"

    }

  ]

}

I just don't know, how to access deeper values in JSON-Oject.

Thanks for helping me!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FVBA-tools%2FVBA-JSON%2Fissues%2F191&data=04%7C01%7C%7C149c620096fc4eceade708d8cb9e15fc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637483227114565097%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=wgMZj24WCP74P%2FZVq5Cq7c6dq50llw4eVIBy47Imvm8%3D&reserved=0, or unsubscribehttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAIKTRA5NEZ2PT56YAIRKDATS53SJLANCNFSM4XHYP34A&data=04%7C01%7C%7C149c620096fc4eceade708d8cb9e15fc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637483227114575100%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=5AICrCyB97FkvmuAbzHdlWm%2FRrxjqHfb%2BGWNNP0iYQw%3D&reserved=0.

[aholden10]

You do need to stack the keys to create the path to your values, and you can't ignore the arrays in that path. Sometimes examples using your own data help, so here are the paths to all your values: 'devices'[0]'data'[0]'id' => 0'devices'[0]'data'[0]'name' => 'Hello''devices'[0]'data'[1]'id' => 1'devices'[0]'data'[1]'name' => 'How are you?''devices'[0]'data'[2]'id' => 2'devices'[0]'data'[2]'name' => 'Bye''devices'[0]'type' => 'LORA''includedTypes'[0] => 'LORA' Good luck! On Sunday, February 7, 2021, 02:25:08 PM EST, Ambientolo notifications@github.com wrote:

Hello there,

I would like to get data from a JSON-Object, that I got from a Rest-API, with VBA to display some data into an Excel-Worksheet.

I have the following JSON-Object: { "devices": [ { "data": [ { "id": 0, "name": "Hello" }, { "id": 1, "name": "How are you?" }, { "id": 2, "name": "Bye" } ], "type": "LORA" } ], "includedTypes": [ "LORA" ] }

I want to get the objects in the array from "data".

My VBA-Code is this: Dim js1Object As Object Dim response1 As String strUrl = "https://XXXXXXXXXXXdevices Set hReq = CreateObject("MSXML2.XMLHTTP") With hReq .Open "GET", strUrl, False .SetRequestHeader "Authorization", "Bearer " & apitoken .Send response1 = hReq.responseText MsgBox response1 Set js1Object = JsonConverter.ParseJson(response1) j = 31 For Each item In js1Object("devices") ws.Cells(j, 7) = item("id") ws.Cells(j, 10) = item("name") j = j + 1 Next MsgBox (response1) End With

How can I access the values from "data"?

If the JSON would look like the object below, my code would work. But my problem is, that the response that I get, is more nested and I can't directly access "data". { "devices": [ { "id": 0, "name": "Hello" }, { "id": 1, "name": "How are you?" }, { "id": 2, "name": "Bye" } ] }

I just don't know, how to access deeper values in JSON-Oject.

Thanks for helping me!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[Ambientolo]

thank you so much for your help! My code is working ;), I have just started learning VBA and I need a little time to understand how it works. now I have another problem. I will try to solve it and in case I cannot I will ask a new question.

Thanks again ;)