How to Pull JSON Data from an API in VBA

Do you have API and want to fetch data in Excel using VBA? You are in the right place.

In this article, you will understand how to fetch API data in VBA. So, let us get started!

Note: We are using jService.io open-source API to pull random questions and their answers.

Create MS Excel Macro-Enable file
Setup VBA Editor
Create a Script to Fetch API Data
Parsing JSON data
Assign Macro to Button
Running Script

Step 1: Create MS Excel Macro-Enable file

Create a new MS Excel Macro-Enable file with the name APITest.xlsm.
Add a button from the menu Developer->Insert->Button with the name Get Random Question.
As well as add text Question Text in cell A5 and Answer in A6 as shown in the image.

Step 2: Setup VBA Editor

Open the VBA editor by pressing Alt+F11 or from the menu Developer->Visual Basic.
In VBA Editor, insert a new module from the menu Insert->Module. The new module Module1 will be added to your VBA Project.
Add reference by menu Tools->References and check the Microsoft Scripting Runtime library, as shown below image.

Step 3: Create a Script to Fetch API Data

Next, we need to create a script that will fetch API data from jService.io. Just copy the below code and paste it into Module1.

Sub ExtractJSONData()
    '''Extract random question and answer from API and show them in Excel.
    Dim URL As String
    Dim httpREQ As Object
    Dim JSON As Object
    Dim responseData As Variant
    
    URL = "https://jservice.io/api/random"
    
    Set httpREQ = CreateObject("MSXML2.XMLHTTP.6.0")
    With httpREQ
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "application/json"
        .send
    End With    
    responseData = httpREQ.responseText
End Sub

Step 4: Parsing JSON data

Now we have successfully fetched data from API. When you print responseData variable, you will see below the JSON response.

[{"id":206257,"answer":"charley horse","question":"I'd love to get some shut-eye but these \"equine\" muscle spasms in my legs don't help, nor do my cries of agony","value":1000,"airdate":"2021-07-06T19:00:00.000Z","created_at":"2022-12-30T21:53:42.341Z","updated_at":"2022-12-30T21:53:42.341Z","category_id":26015,"game_id":7074,"invalid_count":null,"category":{"id":26015,"title":"resisting a rest","created_at":"2022-12-30T21:53:40.765Z","updated_at":"2022-12-30T21:53:40.765Z","clues_count":5}}]

We need to parse this JSON data to get question and answer fields. There are many predefined scripts available online that can be used to parse this JSON data.
For this tutorial, we are going to use https://github.com/VBA-tools/VBA-JSON script to parse JSON data. Open this URL in your web browser.
Then download JsonConverter.bas file and import it in your VBA Editor by menu File->Import File and select JsonConverter.bas file.
You will see JsonConverter in your VBA Project Explore as shown in the below image.

import-jsonconverter-module-in-vba-project

Now, add the following code at the end of your function FetchAPIData() in Module1.

Set JSON = JsonConverter.ParseJson(responseData)
Cells(5, 2) = JSON(1)("question")
Cells(6, 2) = JSON(1)("answer")

The final code in Module1 should look similar to the below code.

Sub ExtractJSONData()
    '''Extract random question and answer from API and show in Excel.
    Dim URL As String
    Dim httpREQ As Object
    Dim JSON As Object
    Dim responseData As Variant
    
    URL = "https://jservice.io/api/random"
    
    Set httpREQ = CreateObject("MSXML2.XMLHTTP.6.0")
    With httpREQ
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "application/json"
        .send
    End With    
    responseData = httpREQ.responseText    
    Set JSON = JsonConverter.ParseJson(responseData)
    Cells(5, 2) = JSON(1)("question")
    Cells(6, 2) = JSON(1)("answer")    
End Sub

Step 5: Assign Macro to Button

Close VBA Editor if it is already open.
Right-click on the Get Random Question button and assign macro ExtractJSONData() to it.

Step 6: Running Script

Click on the Get Random Question button, it will fetch random question and answer in cell B5 and B6 by API request, as shown below image.

Post Views: 2

Table of Contents