0

I have below VBA code to fetch API data. I fetched the data successfully and also converted from JSON format to dictionary format by using JSON converted code. Now I am struggling to convert this dictionary format data into excel table.

Sub ReadFromAPI()

    ' Set the request from the API
    Dim request As New WinHttpRequest
    request.Open "GET", "https://api.com"
    
    ' Send the request
    request.Send
    
    ' Check the result
    If request.Status <> 200 Then
        MsgBox request.ResponseText
        Exit Sub
    End If
    
    ' Convert the response from JSON to a Dictionary structure
    Dim response As Object
    Set response = JsonConverter.ParseJson(request.ResponseText)
    
   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   'VBA Code need to be added here to Access data  from dictionary format into Excel table
   ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

value in Dictionary format in local variable(response) Out put received in Dictionary format

sample of dictionary out put out put in dictionary format

Output data need to be converted into below excel table

Output table format

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Muneeb
  • 83
  • 2
  • 12
  • 2
    `response` will be a Collection of Dictionary objects, so start by looping over that. – Tim Williams Oct 18 '22 at 21:22
  • 1
    ...you're making it difficult for folks to spin up a working example by including the JSON as an image, instead of plain text which can be copied and pasted. – Tim Williams Oct 18 '22 at 21:53
  • Study [this answer](https://stackoverflow.com/a/46245469/4717755) to understand how the JSON converter parses the JSON into a structure of different objects. Then [this answer](https://stackoverflow.com/a/60511987/4717755) gives an example of how you can set up a loop to run through the JSON data. – PeterT Oct 19 '22 at 12:23

0 Answers0