Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
VBA: JSON API -> Excel
Sub js_json_api_example()
Dim req As MSXML2.ServerXMLHTTP60
Dim apiURL, ret As String
Set req = New MSXML2.ServerXMLHTTP60
' Example API: Openweathermap.org
apiURL = "https://samples.openweathermap.org/data/2.5/weather?lat=35&lon=139&appid=439d4b804bc8187953eb36d2a8c26a02"
' Verbindung herstellen
req.Open "GET", apiURL, False
req.send
' Status in Zelle A1 ausgeben
Range("a1").Value = req.Status & " - " & req.statusText
' JSON unformatiert in A2 ausgeben
ret = req.responseText
Range("a2").Value = ret
' JSON parsen
Dim jsonObject As Object
Set jsonObject = JsonConverter.ParseJson(ret)
' Variable Temp ausgeben (in Celsius umgerechnet, daher -273.15)
Range("a3").Value = Round(jsonObject("main")("temp") - 273.15)
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.