VBA: Wie man JSON aus einer API in Excel importiert

Ich musste JSON Daten aus einer REST API in Excel importieren. Die letzten Jahre habe ich mit VBA nicht viel gemacht, mit fehlt also die Übung. Hier dokumentiere ich kurz, wie ich die Daten mit etwas VBA in Excel bekomme.

1- Verweise aktivieren

✅ Microsoft XML, v6.0
✅ Microsoft Scripting Runtime

2- VBA-JSON installieren

1. Download: https://github.com/VBA-tools/VBA-JSON
2. JsonConverter.bas aus Download in das Projekt importieren (VBA Editor öffnen, Alt + F11; Datei > Datei importieren)

3- VBA Code

1. Rechtsklick auf Module > Einfügen > Modul
2. Code:

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

Code auf Github anzeigen

4- Ergebnis

• In Zelle „A1“ erscheint der Status des Requests
• In Zelle „A2“ erscheint das unformatierte JSON Objekt
• In Zelle „A3“ erscheint die in °C umgerechnete Temperatur aus dem JSON Objekt

Quellen & weiterführende Links:

https://medium.com/automation-generation/for-excel-persons-how-to-pull-market-data-with-vba-fca431bb7332

https://github.com/VBA-tools/VBA-JSON

http://excelerator.solutions/2017/08/16/import-json-to-excel-using-vba/

https://medium.com/automation-generation/using-vba-and-excel-to-make-authenticated-requests-for-alpacas-trading-api-2968acaa3776

http://excelerator.solutions/2017/08/28/excel-http-get-request/

https://wellsr.com/vba/2019/excel/vba-http-get-requests-api-serverxmlhttp60/

https://simpleexcelvba.com/how-to-get-http-request-with-xmlhttprequest/

https://technet.rapaport.com/Info/Prices/SampleCode/VBA_Example.aspx