Getting data from URL into MS Access table

Question

There's a website that provides weather data: https://www.visualcrossing.com/weather/weather-data-services

I want to import that data into a table with a button click. The discussion below seems useful to that, but I am not a strong enough VBA coder to translate this to a functional script.
Get data from URL using Excel VBA

Here's an example of the URL data I would like to move into a table. https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/garden%20city%2C%20tx?unitGroup=us&elements=datetime%2Ctempmax%2Cprecip%2Cpreciptype%2Cwindgust%2Cwindspeed&include=days&key=KVP7BF7EYTKGZK236GEFF4RGX&contentType=csv

Can anyone help guide me in how to do this?

Answer 1

You can use my function DownloadFile found on GitHub VBA.PictureUrl and this code:

Public Function LinkData()

    Dim Url         As String
    Dim FileName    As String
    Dim Success     As Long
    
    Url = "https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline/garden%20city%2C%20tx?unitGroup=us&elements=datetime%2Ctempmax%2Cprecip%2Cpreciptype%2Cwindgust%2Cwindspeed&include=days&key=KVP7BF7EYTKGZK236GEFF4RGX&contentType=csv"
    FileName = "C:\Test\WeatherData.csv"
    
    Success = DownloadFile(Url, FileName)
    If Success = 0 Then
        DoCmd.TransferText acLinkDelim, , "WeatherData", FileName, True
    End If

End Function

Call this from the button click:

Private Sub YourButton_Click()

    LinkData

End Sub

That will download and link the file as a table ready to use: