Current Local Weather Report in Excel

City Weather Report in Excel

Excel Vba code in this page gets current local weather report using free api from openweathermap. To use this code, you need these things in advance:

1. API key from openweathermap
2. Accepted City Names

Register with you email in the api provider website & they will provide free key. It takes some time for the key to get activated. Once it is activated, the below code will fetch current weather report to Excel. Note: Get valid city names from this JSON file.

Sub GetWeatherRepor(sCity As String, sApiKey As String)
    Dim oWeb As Object
    Dim sURL As String
    Dim oSh As Worksheet
    
    'Weather Report API URL
    'units=metric - Celcius
    'units=imperial - Farenheit
    sURL = "http://api.openweathermap.org/data/2.5/weather?q=" & sCity & "&units=imperial&appid=" & sApiKey

    'Create Object to get data from web
    Set oWeb = CreateObject("Microsoft.XMLHTTP")
    
    'Extract Weather Data from Api
    oWeb.Open "GET", sURL, False
    oWeb.send
    
    'Prase Temperature Data
    sJSONData = oWeb.responsetext
    sJSONData = VBA.Replace(sJSONData, ":", ",", , , vbTextCompare)
    sJSONData = VBA.Replace(sJSONData, "}", ",", , , vbTextCompare)
    sJSONData = VBA.Replace(sJSONData, """", ",", , , vbTextCompare)
    vdata = VBA.Split(sJSONData, ",", , vbTextCompare)
    
    'Display Important Weather Report Parameters
    For i = 0 To UBound(vdata)
        Select Case vdata(i)
        Case "temp"
            Debug.Print "Temperature: " & vdata(i + 2)
        Case "description"
            Debug.Print "Description: " & vdata(i + 3)
        Case "pressure"
            Debug.Print "Pressure: " & vdata(i + 2)
        Case "humidity"
            Debug.Print "Humidity: " & vdata(i + 2)
        End Select
    Next

End Sub

The above example gets JSON data from the API & parses few weather parameters like temperature, pressure, humidity etc., directly through string operations. This is not the right way to do this The returned data is in JSON format.

Parse JSON from Weather Report

JSON is a plain text format used mostly in data transfer from web servers. There is no built in function available in VBA to parse JSON data. But there is a module available in the github free to use. Get the Code to parse JSON from this link- Click here to get know about this. Note: The same detail can be fetched in other programming languages that can send & receive data from the API. For example Python, php, C, C#, Google script etc.,