Connect To API Excel VBA Macro (Part 1)

In this post we learn how to get data from a web API in Excel using VBA macros. As an example, we are getting data form a public API with information about universities around the world. This API does not require any API key to be used, and consists of a simple URL that accepts a single parameter to specify the country of interest. The Excel VBA macro code to send an API call and retrieve information or data from the API consists of two steps. In this post we will cover the first step, which consists of sending an HTTP request to the API URL, in a similar way we have done to scrape web data in a previous post (Scrape Web Data in Excel Part 1). In the next post we will see how to read the API response returned as JSON format, and retrieve the list of universities for a given country.

Macro/VBA code:

 

  Sub SendAPIcall()

      'Previously add Microsoft XML v6.0 Library

      Dim HTTPreq As Object, country As String, url As String, response As String

 

      Set HTTPreq = CreateObject("MSXML2.XMLHTTP")

 

      country = "Sweden"     

      url = "http://universities.hipolabs.com/search?country=" & country

 

      'Send HTTP request to the API URL

      With HTTPreq

          .Open "GET", url, False

          .send

      End With

   

      response = HTTPreq.responseText

      Debug.Print response  'it shows in Immediate Window

      'Call GetAPIdata(response) (Part 2)

  End Sub

 

Macro explained:

• First of all, we need to add the Microsoft XML v6.0 library to the references in the VBA editor under Tools->References. This library allows to send and work with the XML HTTP request to GET data from the web.
• We declare HTTPreq as an object and later create and assign an XML object to it (late binding). We could also do it in one line (early binding) depending on the library/version. We declare two other variables to store the country and the API HTTP response as text (JSON raw data).
• The url variable stores the target API URL. In this case, that’s a public API that returns the list of universities for a given country. The API does not require any API key to be used.
• Then we open an HTTP connection request using three arguments: The request method, in this case is a GET request, the target web API URL, and a third parameter indicating whether the call is asynchronous (False). After that, we send the request.
• We assign the HTTP response as text to a variable (response). We can visualize the contents of that variable in the immediate window using Debug.Print. That’s the raw JSON (JavaScript Object Notation), which is the most common data format used with APIs.
• Reading the API JSON response will be covered in the next post (Part 2). We will use a simple method to manipulate the raw JSON text in order to get the information of interest. There are many other ways to do that though, and, ultimately, there’s a VBA JSON converter available on GitHub to do the job (a bit more complex but very reliable and possibly applicable to any API JSON response).

This is how we Connect To A Web API in Excel with VBA macros.

Other examples:

• How to Sort Elements In Array with Excel VBA macros
  
• How to Sort Unique Text Values In Cell with Excel VBA macros
  
• How to Process HTTP response to Scrape Web Data with Excel VBA macros