Excel is probably one of the most used tools in this world, so the demand for integrations with extremely complex spreadsheets is a recurring scenario. APIs allow easy access to information in systems, which is becoming more and more standard in the market, with that in mind, some demands of connecting to systems via API in Excel are necessary and very useful, so I decided to share a little how I created this integration. Let's learn how to query Rest APIs using VBA and convert the result to JSON for use in the spreadsheet.
This article hopes you will know the basics of Excel and VBA, as well as what is an API and how it works . Our goal will be to consult a Public Pokemon API and list the result in the tab results .
Creating a blank worksheet
First let's create a blank sheet with macro enabled, inside it I'll create a tab called results .
Creating the macro to query the API
by shortcut alt + f11 let's open the Excel macro editor, and create a module called list pokemons .
Importing the VBA-JSON library
As the API we're going to query returns a JSON as an answer we will need to import the library VBA JSON , it will take care of all the boring work of translating the JSON and returning as an array and object. Installation is very simple, just download the latest version here and in the macro editor go to File > Import File > JsonConverter.bas .
Enabling Microsoft Scripting Runtime
We also need to enable Microsoft Scripting Runtime, to do this just browse Tools > References and search and enable in the list the Microsoft Scripting Runtime .
Creating the VBA macro to query the REST API
Below is the complete code for our request, it might sound scary, but don't worry, I'll explain what each part is doing:
Sub listPokemons()
Dim json As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet
Dim objHTTP As Object
'We selected our results sheet
Set ws = Worksheets("results")
'We create our request object and send
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://pokeapi.co/api/v2/pokemon"
objHTTP.Open "GET", URL, False
objHTTP.Send
strResult = objHTTP.responseText
json = strResult
Set objectJson = JsonConverter.ParseJson(json)
'We create the header cells
ws.Cells(1, 1) = "name"
ws.Cells(1, 2) = "link"
'We loop the results property of the API response
i = 2 'We will start the counter on line 2
For Each pokemon InJsonObject("results")
ws.Cells(i, 1) = pokemon("name")
ws.Cells(i, 2) = pokemon("url")
i = i + 1
next
End Sub
First we define all the variables that we will use in our scripts, including the VBA JSON library import that we previously imported into our project.
Dim json As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
Dim objHTTP As Object
Then we select the spreadsheet we want to display the results of the API query, in our case Worksheets("results") and then we create an object that will allow us to make the request to the API. https://pokeapi.co/api/v2/pokemon . We'll take the answer and put it in the variable json , for now it is nothing more than a text.
'We selected our results sheet
Set ws = Worksheets("results")
'We create our request object and send
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://pokeapi.co/api/v2/pokemon"
objHTTP.Open "GET", URL, False
objHTTP.Send
strResult = objHTTP.responseText
json = strResult
Here where the magic happens, the function parsejson from the VBA JSON library converts the text of our variable json for an accessible object in our script. We are now able to access all properties programmatically in our code.
Set objectJson = JsonConverter.ParseJson(json)
Now that we have the result of our API accessible, we create in the first row of the spreadsheet the header containing the columns Name and link .
'We create the header cells
ws.Cells(1, 1) = "name"
ws.Cells(1, 2) = "link"
Now before analyzing the script we need to understand the result of the API. If you open the link https://pokeapi.co/api/v2/pokemon in your browser you will see the following result:
{
"count": 964,
"next": "https://pokeapi.co/api/v2/pokemon?offset=20&limit=20",
"previous": null,
"results": [
{
"name": "bulbasaur",
"url": "https://pokeapi.co/api/v2/pokemon/1/"
},
{
"name": "ivysaur",
"url": "https://pokeapi.co/api/v2/pokemon/2/"
},
{
"name": "venusaur",
"url": "https://pokeapi.co/api/v2/pokemon/3/"
},
{
"name": "charmander",
"url": "https://pokeapi.co/api/v2/pokemon/4/"
},
{
"name": "charmeleon",
"url": "https://pokeapi.co/api/v2/pokemon/5/"
},
{
"name": "charizard",
"url": "https://pokeapi.co/api/v2/pokemon/6/"
},
{
"name": "squirtle",
"url": "https://pokeapi.co/api/v2/pokemon/7/"
},
{
"name": "wartortle",
"url": "https://pokeapi.co/api/v2/pokemon/8/"
},
{
"name": "blastoise",
"url": "https://pokeapi.co/api/v2/pokemon/9/"
},
{
"name": "caterpie",
"url": "https://pokeapi.co/api/v2/pokemon/10/"
},
{
"name": "metapod",
"url": "https://pokeapi.co/api/v2/pokemon/11/"
},
{
"name": "butterfree",
"url": "https://pokeapi.co/api/v2/pokemon/12/"
},
{
"name": "weedle",
"url": "https://pokeapi.co/api/v2/pokemon/13/"
},
{
"name": "kakuna",
"url": "https://pokeapi.co/api/v2/pokemon/14/"
},
{
"name": "beedrill",
"url": "https://pokeapi.co/api/v2/pokemon/15/"
},
{
"name": "pidgey",
"url": "https://pokeapi.co/api/v2/pokemon/16/"
},
{
"name": "pidgeotto",
"url": "https://pokeapi.co/api/v2/pokemon/17/"
},
{
"name": "pidgeot",
"url": "https://pokeapi.co/api/v2/pokemon/18/"
},
{
"name": "rattata",
"url": "https://pokeapi.co/api/v2/pokemon/19/"
},
{
"name": "raticate",
"url": "https://pokeapi.co/api/v2/pokemon/20/"
}
]
}
We are interested in the property. results , an array containing a list of pokemons with their names and links to more details. We will access this matrix at jsonobject("results") and we will loop to display each pokemon result in a new row of our table.
'We loop the results property of the API response
i = 2 'We will start the counter on line 2
For Each pokemon InJsonObject("results")
ws.Cells(i, 1) = pokemon("name")
ws.Cells(i, 2) = pokemon("url")
i = i + 1
next
If everything goes as expected, by pressing f5 to run our macro, in your spreadsheet you should see the following result:
Conclusion
This was a very simple example of a query, with the object of HT TP it is possible to perform all types of requests, GET, POST, UPDATE, … The interesting thing is to understand how the request is made and how you can display the result, thanks to the VBA JSON library, which already drastically reduces the work required. Now you just need to adapt this flow and script to your needs.
That code worked for me: Sub listPokemons() Dim json As String Dim jsonObject As Object, item As Object Dim i As Long Dim ws As Worksheet Dim objHTTP As Object 'We selected our results sheet Set ws = Worksheets("results") 'We create our request object and send Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") Url = "https://pokeapi.co/api/v2/pokemon" objHTTP.Open "GET", Url, False objHTTP.Send strResult = objHTTP.responseText json = strResult Set objectJson =… Read more »
Hi, THanks for the great explaination!
When I tried to connect my api with the code, I kept getting below error..
I believe its from json vba converter but do you have any advice please?
Err.Raise 10001, “JSONConverter”, json_ParseErrorMessage(JsonString, json_Index, “Expecting ‘{‘ or ‘[‘”)
There are a couple of mistakes in the code as some people have pointed out. jsonObject is declared, but then a variable called objectJson is set. The variable should instead be jsonObject “For Each pokemon InJsonObject” is missing a space, it should be “For Each pokemon In jsonObject” In addition, it makes it easier to debug if you set Option Explicit at the top of the module, which forces you to explicitly declare all variables. This code should work (tested on Microsoft Excel 365): Option Explicit Sub listPokemons() Dim json As String, Url As String, strResult As String Dim jsonObject… Read more »
Guys you made a mistake, please fix it.
For Each pokemon In objectjson("results")warning bros….
DimjsonObjectAs
Object, item
As
Objectno match with
SetobjectJson = JsonConverter.ParseJson(json)jsonObject Not equal objectJson
Hello ,
I am getting error at objHTTP.Send not able to call api,can you please help.
Thanks,
Rayudu.