Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment