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)
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
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
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