How do I access a historical weather API from open-meteo in Excel using VBA?

Question

Trying to get the weather from open-meteo.com into Excel via VBA, and I am getting an error for historical data. The error is:

"Run-time error '-2146697209 (800c0007)' No data is available for the requested source."

I can see the source in my web browser and call it from Python.

Public Sub openWeather()
   
    Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
    myurl = "https://archive-api.open-meteo.com/v1/archive?latitude=40.80&longitude=-74.31&start_date=2023-01-27&end_date=2023-02-23&hourly=temperature_2m"
    xmlhttp.Open "GET", myurl, False
    xmlhttp.send
    MsgBox (xmlhttp.responseText)
    
End Sub

Note I'm also not having this issue substituting their forecast API (e.g., this target). Any suggestions?

Answer 1

Public Sub openWeather()
    Dim xmlhttp As Object, myurl As String

    myurl = "https://archive-api.open-meteo.com/v1/archive?latitude=40.80&longitude=-74.31&start_date=2023-01-27&end_date=2023-02-23&hourly=temperature_2m"

    On Error Resume Next
    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    If Err.Number <> 0 Then
        Exit Sub
    End If
    On Error GoTo 0

    xmlhttp.Open "GET", myurl, False, "", ""
    xmlhttp.send
    If xmlhttp.ReadyState = 4 Then
        MsgBox (xmlhttp.responseText)
    Else
        MsgBox ("xmlhttp.ReadyState =" & xmlhttp.ReadyState )
    End If
End Sub