How to build a simple weather app in Excel VBA

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

weather in vba

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

http://api.openweathermap.org/data/2.5/weather?apikey=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=newyork

It returns response like this

weather API response

Look at the URL parameters mode=xml, units=imperial, apikey=YourAPIKey and 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

Code explanation

  • 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. SelectNodes method 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)

Press 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.

Understanding XPath

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

Wrapping up

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.

Ranjith kumar
3.5 2 votes
Article Rating
Subscribe
Notify of
guest

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Robert Cooper
Robert Cooper
5 years ago

Is there a MAC OS equivalent to the httprequest available in Excel?

John Wong
John Wong
5 years ago

Slight modification with newer versions of Excel:

in Tools -> References, check off MS XML, v6.0
in the second line, MSXML2.xmlhttp needs to be changed to MSXML2.XMLHTTP60
also in the second line, DOMDocument needs to be changed to MSXML2.DOMDocument60

Huron River
Huron River
3 years ago
Reply to  John Wong

Can you please take a look at my code? It still doesn’t work…

Public Sub getWeather()
Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String, xmlresponse As New MSXML2.DOMDocument60
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

Benjamin Holloway
Benjamin Holloway
2 years ago

This is pretty neat but isn’t functional as is. Just an FYI for anyone who has come across this page