Print each item in a JSON Object as separate row in excel file using VBA

Question

I'm trying to read the json from an URL, and paste the json object items as a separate row in an excel sheet.

But till now, I'm able to fetch the entire json object into the excel in single column A1.

I tried to iterate through the json objects and print in separate rows.

But getting 424 Error : Object Qualifier

Need your help on the same.

Sample Data : {"data":{"id":3,"name":"true red","year":2002,"color":"#BF1932","pantone_value":"19-1664"},"support":{"url":"https://reqres.in/#support-heading","text":"To keep ReqRes free, contributions towards server costs are appreciated!"}}

Private Sub HTML_VBA_Extract_Data_From_Website_To_Excel()
Dim oXMLHTTP As Object
Dim sPageHTML  As String
Dim sURL As String

'Change the URL before executing the code. URL to Extract data from.
sURL = "https://reqres.in/api/products/3"

'Extract data from website to Excel using VBA
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
oXMLHTTP.Open "GET", sURL, False
oXMLHTTP.send
sPageHTML = oXMLHTTP.responseText

Dim jsonObject As Object
Set jsonObject = JsonConverter.ParseJson(sPageHTML)

Dim i As Integer
Dim Item As Variant
i = 2
For Each Item In jsonObject.Keys
    ThisWorkbook.Sheets(1).Cells(i, 1).Value = Item
    i = i + 1
    Next

MsgBox "XMLHTML Fetch Completed"

End Sub

Answer 1

This outputs:

data
support
--------
id             3 
name          true red
year           2002 
color         #BF1932
pantone_value 19-1664

to the Immediate window, as expected.

Private Sub JsonTester()
    
    Dim oXMLHTTP As Object
    Dim sURL As String
    Dim jsonObject As Object
    Dim Item As Variant, data As Object
    
    sURL = "https://reqres.in/api/products/3"
    
    Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    oXMLHTTP.Open "GET", sURL, False
    oXMLHTTP.send
    
    Set jsonObject = JsonConverter.ParseJson(oXMLHTTP.responseText)
    
    For Each Item In jsonObject.keys
        Debug.Print Item
    Next
    
    Debug.Print "--------"
    
    Set data = jsonObject("data")
    For Each Item In data.keys
        Debug.Print Item, data(Item)
    Next
End Sub