One of our readers posted a question on forum asking how to get weather data using VBA. So I created a very simple weather app in Excel VBA. It looks like gif below
Understanding weather API
I’m using a free weather API from https://openweathermap.org/ to fetch the weather data for a city. Don’t get scared When I say API. It’s a simple URL which has to be called with few parameters and it returns the weather data in JSON, XML or HTML format.
Try copy pasting this URL into your browser
It returns response like this
Look at the URL parameters
q=newyork. Parameter name says it all. VBA supports XML built-in so I used mode=xml to return data in XML format.
Imperial units for getting the temperature in Fahrenheit, q parameter for the city name and API key for authentication. Read complete documentation of the API here – Weather API Documentation
I’ve given my API key here but use your own API key by signing up – Openweathermap API key. It’s FREE mate!
Using weather API in VBA
So all we have to do is call the API and parse the response to get weather data for a city. Have a look at the code once
Public Sub getWeather() Dim xmlhttp As New MSXML2.xmlhttp, myurl As String, xmlresponse As New DOMDocument myurl = "http://api.openweathermap.org/data/2.5/weather?apikey=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & Sheets(1).Range("A2").Value xmlhttp.Open "GET", myurl, False xmlhttp.Send xmlresponse.LoadXML (xmlhttp.responseText) Sheets(1).Range("B2").Value = xmlresponse.SelectNodes("//current/temperature/@value")(0).Text 'MsgBox (xmlresponse.getElementsByTagName("temperature")(0).Attributes(1).Text) Alternate method to parse XML End Sub
- First, we make a simple GET request to weather API with city name which is pulled from cell A2 in sheet 1. Read about HTTP requests in VBA here
- Define a DOM document and load API response into that document using loadXML method.
Finally we parse the temperature data using XPath or any other method.
SelectNodesmethod can be used to select an element by passing XPath. There are different ways to parse XML. I’ve mentioned 2 methods in the above code. Read the official documentation for Dom document VBA to experiment with different methods
How to use code (For VBA beginners)
Alt + F11 in Excel to open VBA code editor then insert a new module and paste the above code. Also, Don’t forget to add a reference to Microsoft XML object to parse XML response. Do it in this way – Tools > references – Microsoft XML X.0 > select and click ok.
Now run the code using F5 or play button in the code editor. If you want to run the code from Excel using a button as shown in the gif above then insert a shape from Insert tab > right click on the shape and assign macro.
I’ve used this XPath –
//current/temperature/@value which means open element with tag
current, then open element with tag
temperature and then get the value of attribute named
value Read more about XPath selectors here – List of all XPath selectors
JSON API can also be used if you are not a fan of XML. Read how to Parse JSON in VBA here and give it a try!
If you have and questions or feedback, comment below.