Pasting API Data into a Worksheet

[Gavin Harrison]

Hi.

Im new to the world of API's and working on a project at work where i need to extract data from an API and to paste this data into a worksheet. Ive looked at various videos etc and seem to be struggling so any help would be appreciated.

I have this code so far which gets the data from a sample API file.

What do i need to add on to get this data to sit in an excel worksheet within the same document.

Thanks

Sub APIcall()

Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Dim json As Object

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://randomuser.me/api/"
blnAsync = True

With objRequest
    .Open "GET", strUrl, blnAsync
    .SetRequestHeader "Content-Type", "application/json"
    .Send
    
    Do While objRequest.readyState <> 4
         DoEvents
    Loop
    
    strResponse = .ResponseText
      
End With

    Debug.Print strResponse

Set json = JsonConverter.ParseJson(strResponse)

Debug.Print json

End Sub

 

[John_w]

What do i need to add on to get this data to sit in an excel worksheet within the same document.

At its simplest, to put the raw data in cell A1:

Range("A1").Value = strResponse

But I'm sure you're really asking how to extract specific records/fields from the JSON data and put them into Excel cells. As you're using the Json Converter library, If you search this forum for "JsonConverter" you should find a few examples showing how to call it to extract your data.

The code at READ json http and return value includes a JSONToCells function which works with JsonConverter and outputs the parsed JSON object (i.e. your json object variable) to Excel cells in a tree layout; the 'leaf' cell of each node is the data value and it contains a comment showing how to reach that data value. Copy the JSONToCells function into your module and call it like this to output your json object to a sheet named "JSON" (which must exist).

    With ThisWorkbook.Worksheets("JSON")
        .Cells.Clear
        JSONToCells json, .Range("A1")
    End With