JSON square bracket - VBA

[ericlch16]

Hello experts,

I have using Convert Excel to JSON Using VBA | Excelerator Solutions as a reference.

I have jsonstring = JsonConverter.ConvertToJson(jsonItems)

where jsonitems is a collection

the jsonstring is an array as it starts with a [ and finish with a ].

I need to remove the [ and ] at the start and end of the string. I can do that by using the MID function but manipulating a string is not good coding practice. How can i remove the [ and ] without changing the string? I read i need to parse it but not sure how. any help?

Thanks,

 

[Edgar_]

If jsonitems is a collection, you can already parse its contents using a loop.

Dim thing
For Each thing In jsonitems
    Debug.Print thing
Next thing

And if you need a specific item, you can refer to it using its index number.
Debug.Print jsonitems(1)

You use the ConvertToJson method when you need the JSON string that has curly brackets for objects, square brackets for collections, etc. For instance, if you wanted to visualize it, you could even add some whitespaces and it would prettify it to help you read it. You usually utilize that method to send a JSON string as a body for a HTTP POST/PUT request.

 

[ericlch16]

If jsonitems is a collection, you can already parse its contents using a loop.

Dim thing
For Each thing In jsonitems
    Debug.Print thing
Next thing

And if you need a specific item, you can refer to it using its index number.
Debug.Print jsonitems(1)

You use the ConvertToJson method when you need the JSON string that has curly brackets for objects, square brackets for collections, etc. For instance, if you wanted to visualize it, you could even add some whitespaces and it would prettify it to help you read it. You usually utilize that method to send a JSON string as a body for a HTTP POST/PUT request.

the issue is the output of the JsonConverter.ConvertToJson(jsonItems) is an array i.e
[
{"id":1
"name":john
}
]

The API does not like the [ at the beginning and the ] at the end. The question is how to get the output without the square brackets?

 

[Edgar_]

I have jsonstring = JsonConverter.ConvertToJson(jsonItems)

The API does not like the [ at the beginning and the ] at the end. The question is how to get the output without the square brackets?

It depends, is the API accepting only one item at a time? then, for example, to send the first object:
jsonstring = JsonConverter.ConvertToJson(jsonItems(1))