[Solved]-How To Call Rest API From Excel Macros VBA And Parse Json

 Hello everyone! In this post, we'll explore how to make a REST API call from VBA. This is what we'll be focusing on today. We'll use a sample API that returns dummy user data. You can download the source code at the bottom of the post.

We are going to cover the below point in this article

• Getting a JSON response from a REST API with VBA excel
• How do JSON POST requests in Excel VBA
• Parse API response data in VBA

And this is the URL: https://reqres.in/api/users/2

{
"data": {
"id": 2,
"email": "[email protected]",
"first_name": "Janet",
"last_name": "Weaver",
"avatar": "https://reqres.in/img/faces/2-image.jpg"
},
"support": {
"url": "https://reqres.in/#support-heading",
"text": ""
}
}

if you make an API call with this get request, you will get on the dummy data user ID, email,first_name,last_name, image, etc.

How to call rest api from excel vba and parse json response return by rest Api

And this is what we are going to use that in our script.  Excel doesn’t have a built JSON parser. So we are going to use VBA-tools JSON parser which helps us to parse the JSON that we receive after making a get request.

And we have to pass this JSON object as a parameter to this VBA JSON or method so that we can easily parse the object and get a value that we are looking for.

So go to the Git link : https://github.com/VBA-tools/VBA-JSON

So just click on the download code and it will be downloaded in the zip format.

And now go to your Excel sheet. I have already created a blank Excel format. so go to the developer tool and visual basic.

So now you can see the visual basic IDE and go to insert, and insert a form and add two button control on it.

So before writing a script, we need to do some import.

The first thing is we need to import the JSON converter that we download from Github. Go to File and click on Import file. So I have already exported the zip

so this is the folder that I extracted go inside this folder and select the JsonConverter.bas And click on Open.

You can see a new module here, so it imported all the scripts which are present on the Bas file.

So now go back to the Form and click on Tools and select references. And now we are going to deal with a dictionary.

So enable the Microsoft scripting runtime references that you can find it in the list scroll down. So here you can see Microsoft Scripting runtime, select and click on OK.

So let write API calling code on button click of each button i.e GetUser and CreateUser and write code for calling the rest api.

Using Excel and VBA to get rest API data

Click on GetUser and wrute below code

so let me create a variable called objRequest and the data type is the object and we need to store the endpoint URL in a string. So let me create a variable called strUrl .

Private Sub CommandButton1_Click()
    Dim JsonObject As Object
    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String
    
    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    strUrl = "https://reqres.in/api/users/2"
    blnAsync = True

    With objRequest
        .Open "GET", strUrl, blnAsync
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & token
        .Send
    'spin wheels whilst waiting for response
    While objRequest.readyState <> 4
            DoEvents
    Wend
        strResponse = .responseText
    End With
    Set JsonObject = JsonConverter.ParseJson(strResponse)
     MsgBox (JsonObject("data")("email"))
End Sub

It should be the string format and we are going to make the request using XMLHttpRequest and where to make a request, blnAsync as you need to pass a boolean value true or also whether you are making a sync operation or not.

VBA code shows how to make a GET request to an API endpoint, including authentication, and process the response:

CommandButton1_Click() Subroutine: Triggered when CommandButton1 is clicked.

• Initializes variables and objects for HTTP request and response handling.
• Creates an XMLHTTP object (objRequest) to send HTTP requests.
• Specifies the URL of the API endpoint to be accessed (strUrl).
• Defines whether the request should be asynchronous (blnAsync).
• Opens a GET request to the specified URL.
• Sets request headers to specify the content type as JSON and includes an authorization token obtained elsewhere in the code.
• Sends the HTTP request.
• Waits for the response to be received by looping until the readyState of the request object equals 4 (indicating the request is complete).
• Stores the response text in the strResponse variable.
• Parses the JSON response using the JsonConverter.ParseJson method.
• Displays a message box with the email extracted from the JSON response data.

This code illustrates how to retrieve data from an API endpoint using a GET request in VBA, including handling authentication and processing the JSON response.

And finally, after we get the response, we are going to pass it and store it in a variable. name this variable as JsonObject  and data type is an object so that’s it. We have created all the variables that we need to make the HTTP call.

and then filanly we are Parsing String Response From API inVBA. So let’s show this response to a message box and see whether it actually makes an API call or makes sure that we are getting the response or not. we are extracting the “email” from the api response, showing it in a message box.

Now let’s click on “Getuser” button, So we got the response and it successfully makes a get request.

Excel VBA HTTP post request json

Private Sub CommandButton2_Click()
    Dim objHTTP As Object
    Dim Json As String
    Dim Jsonresult As Object
    Json = "{""name"":""Mark Henry"",""job"":""Project Manager""}"
    'here I am pulling creating json body
    Dim result As String

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "https://reqres.in/api/user"
    objHTTP.Open "POST", URL, False

   objHTTP.setRequestHeader "Content-type", "application/json"
   objHTTP.Send (Json)
   result = objHTTP.responseText
    Set Jsonresult = JsonConverter.ParseJson(result)
   MsgBox ("User created with name :" & Jsonresult("name"))

End Sub

we have set the request header using ssetRequestHeader and you have to specify the key-value, Content-type, and application/json. 

VBA code demonstrates how to make a POST request to an API endpoint and process the response:

CommandButton2_Click() Subroutine:

• Triggered when the command button is clicked.
• Initializes variables and objects for HTTP request and JSON processing.
• Defines the JSON payload with a name and job.
• Sets up the HTTP request to the specified URL using the POST method.
• Sets the request header to indicate that the request body is in JSON format.
• Sends the HTTP request with the JSON payload.
• Retrieves the response text from the HTTP request.
• Parses the JSON response using the JsonConverter.ParseJson method.
• Displays a message box with the name of the created user from the JSON response.
• This code illustrates how to send a JSON payload via a POST request in VBA and process the response accordingly.
• And so we have to specify that as an argument here, application/json. we are going to get the response in the form of JSON our next line.

Download Source Code