Connect To API Excel VBA Macro (Part 2)

In the previous post we have seen how to connect to a web API in Excel using VBA macros. In this post we see how to read the API JSON response in Excel and retrieve the data of interest. In this macro example, the data is the list of universities for a given country. The web API is public and does not require any API key to be used. There are different ways to read a JSON file in Excel. We will use a simple method of string manipulation to get the name of each university for a given country. 

Macro/VBA code:

 

  Sub GetAPIdata(response)  'gets response from previous macro (Part 1)

      Dim term As String, termval As String, findterm As Integer

      Dim searchstart As Integer, startpos As Integer, endpos As Integer

 

      ActiveSheet.Columns("A").ClearContents

      term = "name"  'JSON object key for university name

      searchstart = 1

 

      Do

          findterm = InStr(searchstart, response, term, vbTextCompare)

          If findterm > 0 Then searchstart = findterm + 1 Else Exit Do

          startpos = InStr(searchstart, response, ":") + 1

          endpos = InStr(searchstart, response, "}") – 1  'as name is last value

          termval = Mid(response, startpos, endpos - startpos + 1)

          termval = Replace(termval, Chr(34), "")  'removes quotes

          r = r + 1

          ActiveSheet.Range("A" & r).Value = termval

      Loop

  End Sub

 

 

Macro explained:

• We declare several variables to store the searched JSON object key in the raw text (term), object pair value for that term (termval), and positions of the key and value within the JSON raw text string (searchstart, findval, startpos, endpos).
• We need to look at the API JSON response and understand the structure to get to the target data. In this case, we want to get the name of each university for a given country. If you don’t know what JSON is, or you do not understand the JSON structure, I recommend you google it and understand that first. There are plenty of resources and videos on YouTube about that.
• The API JSON response in this example is an array of objects, corresponding to each of the universities in the list. Each object has some info about the university specified with a key, and its corresponding value. The university name is defined with the key “name”, and the name of the university after that, all in quotations. Each key/value pair in the object is separated by a comma. That’s the basis of the JSON format structure. Thus, we set the term variable to “name” and search for that in a Do loop to find each name of a university in the response text.
• We use the VBA InStr function to get the position of the term “name”, and then get the name of the university after the colon sign (“:”). That’s the starting point in the string (startpos) to get the university name. The end position (endpos) is right before the closing curly bracket, as the name is the last element or key/value pair in the object. Otherwise, we would need to look for a comma (that applies to other values).
• Then we remove the quotations, which correspond to Chr(34), from the string outcome (termval). In the scenario of searching other term and targeting the comma instead of curly brackets, the curly brackets would need to be removed from the string too.
• Finally, we add the name of the university (termval) to column A. The process repeats within the loop for as long as there is a next “name” term found. That’s achieved updating the starting position (searchstart) for InStr every time.

 

This is how we Read the API JSON Response in Excel with VBA macros.

Other examples:

• How to Send HTTP Request to Connect to API with Excel VBA macros
  
• How to Sort Elements In Array with Excel VBA macros
  
• How to Sort Unique Text Values In Cell with Excel VBA macros