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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top