Import JSON to Excel

[w4lkman]

Hey guys! I am having an issue importing JSON into Excel. I am using the VBA-JASON converter from GitHub and I the parse command woks fine. However, I can't get the loop working... I get an error saying 'Object is required' for the loop command, but I can't figure it out.. Don't judge me too much, it's my first VBA code hahah

Here's the VBA code that I am using (I have replaced the actual data url for privacy reasons):

Sub JSON()Dim ws As Worksheet
Dim jsonText
Dim test As String
Dim jsonObject As Object, item As Object
Dim i As Long, n As Long


Set ws = Worksheets("Test")
Set jsonText = CreateObject("WinHttp.WinHttpRequest.5.1")
jsonText.Open "GET", "DATA URL HERE"
jsonText.Send
ws.Cells(1, 1) = jsonText.ResponseText
test = ws.Cells(1, 1)
Set jsonObject = JsonConverter.ParseJson(test)
i = 3
n = 1
ws.Cells(2, 1) = "Publisher ID"
ws.Cells(2, 2) = "Clicks"
ws.Cells(2, 3) = "Conversions"


For Each item In jsonObject("response")("data")("data")(n)("Stat")
    ws.Cells(i, 1) = item("Affiliate")("ref_id")
    ws.Cells(i, 2) = item("clicks")
    ws.Cells(i, 3) = item("conversions")
    i = i + 1
    n = n + 1
Next


End Sub

Here's the JSON data that I am using:


Thanks,
Mantas

 

[w4lkman]

{
"request": {
"Target": "Report",
"Format": "json",
"Service": "HasOffers",
"Version": "2",
"NetworkToken": "kSAksaisASJIAsajsaSIasjAS",
"Method": "getStats",
"fields": [
"Stat.conversions",
"Stat.clicks",
"Affiliate.ref_id"
],
"filters": {
"Offer.name": {
"conditional": "EQUAL_TO",
"values": "Offer"
}
},
"data_start": "2018-06-01",
"data_end": "2018-08-01",
"__lc_visitor_id_1040387": "S1501494124.ba10d39246",
"_hp2_id_1318563364": "{"userId":null,"pageviewId":"7618863146391997","sessionId":"1767136814445328","identity":"33613-30","trackerVersion":"4.0","oldIdentity":"33613-30","identityField":null,"isIdentified":1}",
"optimizelyEndUserId": "oeu1504109700190r0.48971650157301994",
"optimizelySegments": "{"3005961667":"direct","3007971800":"ff","3016921538":"false"}",
"optimizelyBuckets": "{"8489131014":"8487312397"}",
"_mkto_trk": "id:210-BFY-977&token:_mch-hasoffers.com-1504109700819-56667",
"__gaTune": "GA1.2.1748037793.1503563832",
"intercom-lou-zrjutp6m": "1",
"_ga": "GA1.2.44981861.1516723164",
"lc_ssoundefined": "1526313866986",
"lc_sso1040387": "1533026282847",
"intercom-session-zrjutp6m": "NkpwaUx2RjNuRzBxbnh3MWZ2L1I4c1pmQjhIaVhVd2oraXNTT0VZcXJVZFIxeWN1U2padVkwN0dVRWlnMlVqZC0tMjdTSjFTdWNOYnNaUGNaWW5iRnZEQT09--c0bb6f4b22d4ecf346c06d1423730a135bf6e2c0",
"_hp2_ses_props_1318563364": "{"r":"http://i/admin/search","ts":1533123551755,"d":"i","h":"/admin/affiliates/view/1002"}"
},
"response": {
"status": 1,
"httpStatus": 200,
"data": {
"page": 1,
"current": 50,
"count": 7,
"pageCount": 1,
"data": [
{
"Stat": {
"conversions": "2",
"clicks": "12"
},
"Affiliate": {
"ref_id": "13"
}
},
{
"Stat": {
"conversions": "2",
"clicks": "4126"
},
"Affiliate": {
"ref_id": "685"
}
},
{
"Stat": {
"conversions": "68",
"clicks": "728449"
},
"Affiliate": {
"ref_id": "2437"
}
},
{
"Stat": {
"conversions": "133",
"clicks": "1282076"
},
"Affiliate": {
"ref_id": "1440"
}
},
{
"Stat": {
"conversions": "166",
"clicks": "1084985"
},
"Affiliate": {
"ref_id": "2218"
}
},
{
"Stat": {
"conversions": "179",
"clicks": "2319358"
},
"Affiliate": {
"ref_id": "2511"
}
},
{
"Stat": {
"conversions": "193",
"clicks": "1284801"
},
"Affiliate": {
"ref_id": "2855"
}
}
],
"dbSource": "branddb"
},
"errors": [],
"errorMessage": null
}
}

 

[John_w]

Do you still need help with this? JsonConverter.Parse gives an error on line 23:

"_hp2_id_1318563364": "{"userId":null,"pageviewId":"7618863146391997","sessionId":"1767136814445328","identity":"33613-30","trackerVersion":"4.0","oldIdentity":"33613-30","identityField":null,"isIdentified":1}",

See https://jsoneditoronline.org/?id=f5470b3a698b460e8e18506349996ed4

Is there an error in your posted JSON? I can't really help if the JSON is invalid.