getpeople_swapi_apicall_excel.vba

Sub getPeople()
    Dim req As MSXML2.ServerXMLHTTP60
    Dim Parsed As Dictionary
    Dim i As Integer
    Dim Results As Collection
    Dim Value As Dictionary

    Set req = New MSXML2.ServerXMLHTTP60
    
    api_url = "https://swapi.dev/api/people/"
    
    'Setting the header
    Worksheets("Sheet2").Cells(1, "A").Value = "name"
    Worksheets("Sheet2").Cells(1, "B").Value = "height"
    Worksheets("Sheet2").Cells(1, "C").Value = "mass"
    Worksheets("Sheet2").Cells(1, "D").Value = "hair_color"
    Worksheets("Sheet2").Cells(1, "E").Value = "skin_color"
    Worksheets("Sheet2").Cells(1, "E").Value = "eye_color"
    Worksheets("Sheet2").Cells(1, "F").Value = "birth_year"
    Worksheets("Sheet2").Cells(1, "G").Value = "gender"
    Worksheets("Sheet2").Cells(1, "H").Value = "homeworld"
    Worksheets("Sheet2").Cells(1, "I").Value = "created"
    Worksheets("Sheet2").Cells(1, "J").Value = "edited"
    Worksheets("Sheet2").Cells(1, "K").Value = "url"
    
    i = 2
    Do While Not (IsNull(api_url))
        req.Open "GET", api_url, False
        req.send
        Set Parsed = JsonConverter.ParseJson(req.responseText)
        Set Results = Parsed("results")

        For Each Value In Results
            Worksheets("Sheet2").Cells(i, "A").Value = Value("name")
            Worksheets("Sheet2").Cells(i, "B").Value = Value("height")
            Worksheets("Sheet2").Cells(i, "C").Value = Value("mass")
            Worksheets("Sheet2").Cells(i, "D").Value = Value("hair_color")
            Worksheets("Sheet2").Cells(i, "E").Value = Value("skin_color")
            Worksheets("Sheet2").Cells(i, "E").Value = Value("eye_color")
            Worksheets("Sheet2").Cells(i, "F").Value = Value("birth_year")
            Worksheets("Sheet2").Cells(i, "G").Value = Value("gender")
            Worksheets("Sheet2").Cells(i, "H").Value = Value("homeworld")
            Worksheets("Sheet2").Cells(i, "I").Value = Value("created")
            Worksheets("Sheet2").Cells(i, "J").Value = Value("edited")
            Worksheets("Sheet2").Cells(i, "K").Value = Value("url")
        i = i + 1
        Next
        api_url = Parsed("next")
    Loop
        
     
End Sub