VBA: JSON API -> Excel

vba_json_to_exel.vba

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