Skip to content

Instantly share code, notes, and snippets.

@julian-stark

julian-stark/vba_json_to_exel.vba

Created September 8, 2020 12:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save julian-stark/6895245270923bc25481d7f30abdb823 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save julian-stark/6895245270923bc25481d7f30abdb823 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. julian-stark created this gist Sep 8, 2020.
    30 changes: 30 additions & 0 deletions vba_json_to_exel.vba
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,30 @@

    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