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):
Here's the JSON data that I am using:
Thanks,
Mantas
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