Json Excel Square Brackets within Square Brackets Run-Time Error '1004'

[emlonett]

Hello, I have a json file which I am parsing to excel, but one of the fields I need to pull doesn't work. The only difference I can see between this field any all other fields, is that this field is within square brackets, whereas all my other fields are not.

I get an error Application Defined or Object defined error at the red point in the code below.

Can you please let me know how I can pull the data from this field? I have highlighted the field below in red that I want to pull.

Json data:

"data": [
{
"id": 1,
"name": "Test",
"partner_ids": [],
"lastUpdatedAt": "2024-01-05T11:23:49+00:00"
},
{
"id": 3,
"name": "XXXX",
"partner_ids": [
9,
10,
11,
12,
13
],
"lastUpdatedAt": "2024-06-25T12:41:14+00:00"
},
{
"id": 4,
"name": "ABCD",
"partner_ids": [
14,
15,
16
],



Code I am using (which works for all other API calls I make, it is just this one field.


Excel Code:

Dim req As MSXML2.ServerXMLHTTP60: Set req = New MSXML2.ServerXMLHTTP60
Dim Transaction As Collection
Dim json As Dictionary
Dim api_url As String
Dim CellRef As Integer
Dim Resp As Object
Dim results As Dictionary
Dim Transactions As ArrayList

api_url = "XXXXXXX"

With req
.Open "GET", api_url, False
'.setRequestHeader "accept", "application/json"
.setRequestHeader "authorization:", " Bearer XXXXXXX"
.send
End With

Set json = JsonConverter.ParseJson(req.responseText)

If req.Status <> 200 Then
MsgBox req.responseText
Exit Sub
End If

Set Resp = JsonConverter.ParseJson(req.responseText)
Set Transaction = Resp("data")
CellRef = 2

With ws2
.Range("A1").Value = "Sub-Operator ID"
.Range("B1").Value = "Name"
.Range("C1").Value = "Partner IDs"

For Each results In Transaction
.Range("A" & CellRef).Value = results("id")
.Range("B" & CellRef).Value = results("name")
.Range("C" & CellRef).Value = results("partner_ids")

CellRef = CellRef + 1
Next results
End With


I have tried turning it into a arraylist instead (which again works elsewhere, but not for this field):
Set Resp = JsonConverter.ParseJson(req.responseText)
Set Transactions = New ArrayList
Transactions.Add Resp("data")

With ws3
For Each results In Transactions
.Range("A" & c).Value = results("partner_ids")
c = c + 1
Next results
End With

I would appreciate any help available! Thank you.

If I have missed any vital information, please let me know.

 

[Domenic]

results("partner_ids") returns a collection. So somehow you'll need to retrieve each item from the collection. So, assuming that you want those items concatenated within the cell, and separated by a comma, try the following...

1) First, add the following function to your module...

Private Function getPartnerIdsFromCollection(ByVal col As Object) As String

    Dim tmp As String
    Dim itm As Variant
    
    tmp = ""
    For Each itm In col
        tmp = tmp & "," & itm
    Next itm
    
    getPartnerIdsFromCollection = Mid$(tmp, 2)
    
End Function

2) Then, in your code, replace...

.Range("C" & CellRef).Value = results("partner_ids")

with

.Range("C" & CellRef).Value = getPartnerIdsFromCollection(Results("partner_ids"))

Hope this helps!

 

[emlonett]

results("partner_ids") returns a collection. So somehow you'll need to retrieve each item from the collection. So, assuming that you want those items concatenated within the cell, and separated by a comma, try the following...

1) First, add the following function to your module...

Private Function getPartnerIdsFromCollection(ByVal col As Object) As String

    Dim tmp As String
    Dim itm As Variant
   
    tmp = ""
    For Each itm In col
        tmp = tmp & "," & itm
    Next itm
   
    getPartnerIdsFromCollection = Mid$(tmp, 2)
   
End Function

2) Then, in your code, replace...

.Range("C" & CellRef).Value = results("partner_ids")

with

.Range("C" & CellRef).Value = getPartnerIdsFromCollection(Results("partner_ids"))

Hope this helps!

Perfect! Thank you so much!

 

[Domenic]

You're very welcome, cheers!