Results 1 to 3 of 3

Thread: Import JSON to Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Import JSON to Excel

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

    Code:
    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

  2. #2
    New Member
    Join Date
    Aug 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Import JSON to Excel

    {
    "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
    }
    }

  3. #3
    MrExcel MVP
    Join Date
    Oct 2007
    Posts
    5,904
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Import JSON to Excel

    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=f54...18506349996ed4

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •