VBA - API call displayed weather in Excel

I am trying to show weather in an Excel sheet. I am extracting the JSON data from the API of open-meteo - https://api.open-meteo.com/v1/forecast?latitude=13.90&longitude=100.53&hourly=relativehumidity_2m,windspeed_180m,temperature_180m

This is my script, but it is not working for some reason.

Public Sub openWeather()
    Dim xmlhttp As Object
    Dim responseData As String
    Dim json As Object
    Dim url As String

    url = "https://api.open-meteo.com/v1/forecast?latitude=13.90&" & _ 
     "longitude=100.53&hourly=relativehumidity_2m,windspeed_180m," & _ 
     "temperature_180m"
    
    On Error Resume Next
    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    If Err.Number <> 0 Then
        Exit Sub
    End If
    On Error GoTo 0
    
    With xmlhttp
        .Open "GET", url, False
        .send
        responseData = .responseText
    End With
    
    Set json = JsonConverter.ParseJson(responseData)
    
    With ThisWorkbook.Sheets("Sheet1")
        Dim hourlyData As Object
        Set hourlyData = json("hourly")
        
        Dim i As Integer
        For i = 1 To hourlyData.Count
            If Not IsObject(hourlyData(i)("windspeed_180m")) And & _
             TypeName(hourlyData(i)("windspeed_180m")) = "Double" Then
                .Cells(i, 3).Value = CDbl(hourlyData(i ("windspeed_180m"))
            Else
                .Cells(i, 3).Value = ""
            End If
    
            If Not IsObject(hourlyData(i)("temperature_180m")) And  & _
             TypeName(hourlyData(i)("temperature_180m")) = "Double" Then
                .Cells(i, 4).Value = CDbl(hourlyData(i)("temperature_180m"))
            Else
                .Cells(i, 4).Value = ""
            End If
        Next i
    
    End With
End Sub

Error:

> Type mismatch
>
> vb
> If Not IsObject(hourlyData(i)("windspeed_180m")) And TypeName(hourlyData(i)("windspeed_180m")) = "Double" Then
>

The API call is successful. Hope anybody is able to help. Thanks in advance.

Ok, it works also with object only. But it's more easy to read the code with the more specific dictionary as type. You can read all values like this:

Public Sub openWeather()
Const url As String = "https://api.open-meteo.com/v1/forecast?latitude=13.90&longitude=100.53&hourly=relativehumidity_2m,windspeed_180m,temperature_180m"
Dim json As Dictionary
Dim ws As Worksheet
Dim dataSet As Long
Dim currRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
currRow = 2
With CreateObject("MSXML2.XMLHTTP.6.0")
.Open "GET", url, False
.send
If .Status = 200 Then
Set json = JsonConverter.ParseJson(.responseText)
For dataSet = 1 To json("hourly")("time").Count
ws.Cells(currRow, 1) = Replace(json("hourly")("time")(dataSet), "T", " ") 'timestamp
ws.Cells(currRow, 2) = json("hourly")("relativehumidity_2m")(dataSet)     'relativehumidity
ws.Cells(currRow, 3) = json("hourly")("windspeed_180m")(dataSet)          'windspeed
ws.Cells(currRow, 4) = json("hourly")("temperature_180m")(dataSet)        'temperature
currRow = currRow + 1
Next dataSet
Else
MsgBox "Page not loaded. HTTP status: " & .Status
End If
End With
End Sub

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

• Data => Get&Transform => Get Data => From Other Sources => Blank Query
• When the PQ Editor opens: Home => Advanced Editor
• Paste the M Code below in place of what you see
• Read the comments and explore the Applied Steps to understand the algorithm

let
//define URL
URL = "https://api.open-meteo.com/v1/forecast?latitude=13.90&longitude=100.53&hourly=relativehumidity_2m,windspeed_180m,temperature_180m",
//Read from web
// note header to allow for the compressed json used on this site
Source = Json.Document(Web.Contents(URL, [Headers=[#"Accept-Encoding"="gzip"]]))[hourly],
//Convert to table
#"Converted to Table" = Record.ToTable(Source),
//Expand the lists of values
#"Expand Lists" = Table.FromColumns(
#"Converted to Table"[Value], #"Converted to Table"[Name]),
//set the data types
#"Changed Type" = Table.TransformColumnTypes(#"Expand Lists",{{"time", type datetime}, {"relativehumidity_2m", Int64.Type}, {"windspeed_180m", type number}, {"temperature_180m", type number}})
in
#"Changed Type"

Partial Results<br>

Try :

Dim windspeed_180m As Double
If Not IsObject(hourlyData(i)("windspeed_180m")) And TypeName(hourlyData(i)("windspeed_180m")) = "Double" Then
.Cells(i, 3).Value = CDbl(hourlyData(i)("windspeed_180m"))
Else
.Cells(i, 3).Value = ""
End If