How to VBA Json parse an output with same name value on multiple levels

larssov

New Member
Joined
Jan 30, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi I have a json outfile that looks like this. As you can see both the name "data" occurs twice in the structre, and below "stages" each stage "ID" have the same name "ID" - I am trying to get all the different stage "id"s into 10 different colums (and then go through all objects to fill those columns)

VBA Code:
"data": [
        {
            "id": "816051",
            "type": "jobs",
            "links": {
                "careersite-job-url": "https://workoab.teamtailor.com/jobs/gdsgds",
                "careersite-job-apply-url": "https://workoab.teamtailor.com/jobs/816051-0220-gdsgds/applications/new?secret=d93b3a3a-c433-4564-a21c-d07096060365",
                "careersite-job-apply-iframe-url": "https://workoab.teamtailor.com/jobs/816051-0220-gdsgds/applications/new?iframe=true&secret=d93b3a3a-c433-4564-a21c-d07096060365",
                "self": "https://api.teamtailor.com/v1/jobs/816051"
            },
            "attributes": {
                "apply-button-text": "",
                "body": "<p>Används ej.</p>",
                "end-date": null,
                "human-status": "unlisted",
                "internal": false,
                "picture": null,
                "pinned": false,
                "start-date": null,
                "status": "unlisted",
                "tags": [],
                "title": "blablablabal",
                "pitch": "Används ej.",
                "external-application-url": "",
                "name-requirement": "optional",
                "resume-requirement": "optional",
                "cover-letter-requirement": "optional",
                "phone-requirement": "optional",
                "created-at": "2020-01-24T09:19:53.940+01:00",
                "sharing-image-layout": "default",
                "mailbox": "job-d93b3a3a-c433-4564-a21c-d07096060365@inbound.teamtailor.com"
            },
            "relationships": {
                "stages": {
                    "links": {
                        "self": "https://api.teamtailor.com/v1/jobs/816051/relationships/stages",
                        "related": "https://api.teamtailor.com/v1/jobs/816051/stages"
                    },
                    "data": [
                        {
                            "type": "stages",
                            "id": "4494024"
                        },
                        {
                            "type": "stages",
                            "id": "4494023"
                        },
                        {
                            "type": "stages",
                            "id": "4494022"
                        },
                        {
                            "type": "stages",
                            "id": "4494021"
                        },
                        {
                            "type": "stages",
                            "id": "4494020"
                        },
                        {
                            "type": "stages",
                            "id": "4494019"
                        },
                        {
                            "type": "stages",
                            "id": "4494018"
                        },
                        {
                            "type": "stages",
                            "id": "4494017"
                        },
                        {
                            "type": "stages",
                            "id": "4494016"
                        },
                        {
                            "type": "stages",
                            "id": "4494015"
                        },
                        {
                            "type": "stages",
                            "id": "4494014"
                        },
                        {
                            "type": "stages",
                            "id": "4494013"
                        },
                        {
                            "type": "stages",
                            "id": "4494012"
                        },
                        {
                            "type": "stages",
                            "id": "4494011"

My code looks like below, but it does not work. it works fine to get for example any of the attributes.

Not sure how I should proceed. Any help would be benficial

VBA Code:
Option Explicit
Sub getdata()
Dim ws As Worksheet: Set ws = Worksheets("Datalastcall")
Sheets("DataLastCall").Select
Dim i As Long
i = 2
Dim last As String
Dim nexturl As String
nexturl = "https://api.teamtailor.com/v1/jobs?include=stages&filter%5Bstatus%5D=unlisted"
Dim http As Object
Dim Url As String
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim JSONresp As String
Dim jsonObject As Dictionary, item As Object

    Url = nexturl
    With http
        .Open "Get", Url, False
        .SetRequestHeader "Accept", "application/vnd.api+json"
        .SetRequestHeader "X-Api-Version", "20161108"
        .SetRequestHeader "Authorization", "Token token=my Token"
        .Send
    End With
JSONresp = http.ResponseText
Set jsonObject = JsonConverter.ParseJson(JSONresp)
Dim p As Long
p = 3
For Each item In jsonObject("data")
                ws.Cells(i, p) = item("relationships")("stages")("data")("id")(1)
                ws.Cells(i, p + 1) = item("relationships")("stages")("data")("id")(2)
                ws.Cells(i, p + 3) = item("relationships")("stages")("data")("id")(3)
                i = i + 1
Next

End Sub

I have tried removing the (1), I have tried to get some type of select case to work, but I cant seem to get it to work
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,114,476
Messages
5,548,259
Members
410,825
Latest member
Dave12
Top