Using Excel and VBA to get API data | Getting a JSON response from a REST API with VBA

by

Here’s an example of how you can use Excel and VBA to get data from a RESTful API. In this example, I’ll use the JSONPlaceholder API (https://jsonplaceholder.typicode.com) as a sample API. We’ll retrieve a list of users and populate it in an Excel sheet.

  1. Enable References:
    • Open Excel and press Alt + F11 to open the VBA editor.
    • Go to Tools > References and enable the “Microsoft XML, v6.0” reference.
  2. Insert a Module:
    • In the VBA editor, insert a new module (Insert > Module).
  3. Write VBA Code:

' VBA code to get data from a RESTful API and parse JSON

Sub GetApiData()
    ' Set your API endpoint URL
    Dim apiUrl As String
    apiUrl = "https://jsonplaceholder.typicode.com/users"

    ' Call the API and get the response
    Dim response As String
    response = GetApiResponse(apiUrl)

    ' Parse JSON response
    Dim json As Object
    Set json = JsonConverter.ParseJson(response)

    ' Process JSON data and populate Excel sheet
    PopulateSheet json
End Sub

Function GetApiResponse(apiUrl As String) As String
    ' Create HTTP request object
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")

    ' Open a connection to the API URL
    xmlHttp.Open "GET", apiUrl, False
    xmlHttp.setRequestHeader "Content-Type", "application/json"
    xmlHttp.send ""

    ' Get the API response
    GetApiResponse = xmlHttp.responseText
End Function

Sub PopulateSheet(json As Object)
    ' Create a new worksheet
    Dim ws As Worksheet
    Set ws = Worksheets.Add

    ' Output headers
    ws.Cells(1, 1).Value = "ID"
    ws.Cells(1, 2).Value = "Name"
    ws.Cells(1, 3).Value = "Username"
    ws.Cells(1, 4).Value = "Email"
    ws.Cells(1, 5).Value = "Phone"

    ' Output data from JSON to the worksheet
    Dim user As Object
    Dim row As Integer
    row = 2 ' Start from row 2 to leave room for headers

    For Each user In json
        ws.Cells(row, 1).Value = user("id")
        ws.Cells(row, 2).Value = user("name")
        ws.Cells(row, 3).Value = user("username")
        ws.Cells(row, 4).Value = user("email")
        ws.Cells(row, 5).Value = user("phone")

        row = row + 1
    Next user
End Sub
  1. Run the Macro:
    • Press F5 or run the macro manually to execute the code.
    • A new worksheet will be created, and the user data from the API will be populated in the sheet.

This is a basic example, and you may need to adjust the code according to the structure of the API response and your specific requirements. Additionally, consider error handling and addressing potential issues such as rate limiting or authentication if your API requires it.

Getting a JSON response from a REST API with VBA

To retrieve a JSON response from a REST API using VBA, you can use the MSXML2.ServerXMLHTTP object. Here’s an example code snippet that demonstrates how to make a simple GET request and handle the JSON response:

Sub GetJsonData()
    ' Set the API endpoint URL
    Dim apiUrl As String
    apiUrl = "https://jsonplaceholder.typicode.com/todos/1"

    ' Call the API and get the JSON response
    Dim jsonResponse As String
    jsonResponse = GetApiResponse(apiUrl)

    ' Parse and display the JSON response
    ParseAndDisplayJson jsonResponse
End Sub

Function GetApiResponse(apiUrl As String) As String
    ' Create HTTP request object
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP")

    ' Open a connection to the API URL
    xmlHttp.Open "GET", apiUrl, False
    xmlHttp.setRequestHeader "Content-Type", "application/json"
    xmlHttp.send ""

    ' Get the API response
    GetApiResponse = xmlHttp.responseText
End Function

Sub ParseAndDisplayJson(jsonResponse As String)
    ' Use a JSON parser (VBA-JSON) to parse the JSON response
    Dim json As Object
    Set json = JsonConverter.ParseJson(jsonResponse)

    ' Access specific properties from the JSON object
    Dim userId As Long
    Dim id As Long
    Dim title As String
    Dim completed As Boolean

    userId = json("userId")
    id = json("id")
    title = json("title")
    completed = json("completed")

    ' Display the parsed data in the Immediate Window
    Debug.Print "UserID: " & userId
    Debug.Print "ID: " & id
    Debug.Print "Title: " & title
    Debug.Print "Completed: " & completed
End Sub

In this example, the GetApiResponse function sends a GET request to the specified API URL and retrieves the JSON response. The ParseAndDisplayJson subroutine uses a JSON parser (such as the one provided by VBA-JSON) to parse the JSON response and extract specific properties.

Make sure to include error handling in your code to handle situations where the API request fails or the JSON parsing encounters issues. Additionally, you may need to adjust the code based on the structure of the JSON response from your specific API.

Leave a comment