JSON Result object dynamically written to Excel Worksheet VBA

Question

I am attempting to write a tabular result (columns and rows of data) from an API call into an Excel range / table.

I managed to get it working, but I'd like for this to be dynamic across data sets (my data sets can be any shape with differing field names). As can be seen in the image, I don't necessarily know that the output is 3 columns of data, but I would want to get this from the JsonResult object (it forms part of the object, but I don't know how to access / reference it).

The object also contains the column names of the data which I would like written to Excel as well (as headings), but I don't know how.

Finally I'd like to write the row data to Excel as well, but I don't know how to access these values without specific reference to them (e.g. "company_code").

TO SUMMARISE: The items in yellow in the screenshot as well as the column / field names should be dynamically read from the JsonResult object.

Any assistance would be appreciated.

JsonResult in text: "[{"company_code":"ABC","employee_code":"5","is_exception":"0"},{"company_code":"ABC","employee_code":"8","is_exception":"1"}]"

My code snippet (if it helps):

Set JsonResult = ParseJson(ParseJson(req.responseText)("results")(1)("findings"))("results")

Dim Values As Variant
ReDim Values(JsonResult.Count, 3)

Dim Value As Dictionary
Dim i As Long

i = 0
For Each Value In JsonResult
  Values(i, 0) = Value("company_code")
  Values(i, 1) = Value("employee_code")
  Values(i, 2) = Value("is_exception")
  i = i + 1
Next Value

Sheets("Sheet1").Range(Cells(1, 1), Cells(JsonResult.Count, 3)) = Values

You should definitely look at my VBA Json parser. There is a routine included for tabular data. https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba/62820310#62820310 — Excel Hero, Jun 09 '21 at 20:47

score 2 · Accepted Answer · edited Jun 10 '21 at 08:38

This is what ended up working for me:

Dim Item As Dictionary
Dim i As Long, size As Long

size = UBound(JsonResult(1).Keys) + 1
[a1].Resize(, size) = JsonResult(1).Keys 'Header rows

i = 1
For Each Item In JsonResult
  i = i + 1
  Range("A" & i).Resize(, size) = Item.Items 'Result rows
Next Item

The keys from the collection / dictionary's first Item is used for the Header rows and the items from each item in the collection / dictionary is written to Excel after that.

Works like an absolute charm.

JSON Result object dynamically written to Excel Worksheet VBA

1 Answers1