How Use VBA To Get Data From API With Bearer Token To Excel

Hello developers,In this post, we will explore how to call an API using Excel VBA with token authentication. 

In this article, we will learn how to use Excel VBA to make REST API requests with HTTP authentication.

We are going to cover the below point in this article

• Excel VBA HTTP request authentication
• How to pass authentication credentials in VBA
• How VBA setRequestHeader “Authorization” 

Let’s understand step-by-step.

Download VBA-tools JSON parser to parse the JSON for easily parse the JSON return from the API.

Link for downloading the package Git link: https://github.com/VBA-tools/VBA-JSON

The download source code and extract the zip format, you can see the JsonConverter.bas file.

Now let’s import the JSON converter that we downloaded from Github. File=>Import file.we already exported the zip select JsonConverter.bas and click on Open.

Go to Tools and select references and add Microsoft scripting runtime references

Now our project ready for the calling the rest api

Using Excel-VBA to get data from API with bearer token

Private Sub CommandButton1_Click()
 GetToken
End Sub


Private Sub GetToken()
    Dim objHTTP As Object
    Dim Json As String
    Dim Jsonresult As Object
    
    Json = "{""email"":""[email protected]"",""password"":""cityslicka""}"
    'here i'm creating the json body for request

    Dim result As String

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

   objHTTP.setRequestHeader "Content-type", "application/json"
   objHTTP.Send (Json)
   result = objHTTP.responseText
    Set Jsonresult = JsonConverter.ParseJson(result)
   GetuserdetailDetail (Jsonresult("token"))
    'MsgBox (Jsonresult.accessToken)
End Sub

Private Sub GetuserdetailDetail(ByVal token As String)
    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("id"))
    With Sheets(1)
            .Cells(1, 1).Value = JsonObject("data")("id")
            .Cells(1, 2).Value = JsonObject("data")("email")
            .Cells(1, 3).Value = JsonObject("data")("first_name")
            .Cells(1, 4).Value = JsonObject("data")("last_name")
            .Cells(1, 5).Value = JsonObject("data")("avatar")
            .Cells(1, 6).Value = JsonObject("support")("url")
    End With
End Sub

if you have any query then let me know in comment thanks.

This Excel VBA code demonstrates how to call an API with token authentication. Here's an explanation of each part:

CommandButton1_Click():

This subroutine is triggered when "CommandButton1" is clicked. It calls the "GetToken" subroutine.

GetToken():

• This subroutine is responsible for obtaining the authentication token. It creates a JSON string representing the user's email and password.
• It initializes an instance of MSXML2.ServerXMLHTTP to make an HTTP POST request to the API endpoint "https://reqres.in/api/login" with the provided JSON data.
• The request's content-type is set to "application/json".
• After sending the request, it retrieves the response and parses it as JSON.
• It then calls the "GetuserdetailDetail" subroutine, passing the obtained token.

GetuserdetailDetail(ByVal token As String):

This subroutine is used to fetch user details using the provided token.

• It initializes an instance of MSXML2.XMLHTTP to make an HTTP GET request to the API endpoint "https://reqres.in/api/users/2".
• The request includes the token in the Authorization header as "Bearer <token>".
• It waits for the response and retrieves it.
• The response is parsed as JSON.The user details obtained from the response are then populated into the Excel sheet's cells.

Above code shows how to authenticate with an API using token-based authentication in Excel VBA and fetch user details from the API endpoint.

When writing code, this function automatically flags errors in the code and suggests changes wherever necessary. Furthermore, users can create fully functional programs by using these suggestions and adding the correct code from the available databases.

Is Microsoft Visual Basic Free?

The development environment is regularly updated with features and the latest version includes the option to add comments to different lines. This means that you can now add lines after a segment as well as line continuation.
Is there any option?

So until that do even right. So until we get a response, this while loop will make the script weight. Once we receive the response, we are going to assign the response to this variable HDR response and the response will be returned to this response. Next we have another out request as one text. So that’s why we are using it out, because still we are inside the with block.

So the next thing is we have to set the request header using set the request heat out and you have to specify the key value, content type and application types. And so we have to specify that as an argument here, type application. So because we are going to get the response in the form of JSON our next line, we have to send this get re. Well, so you can use a send here. So after that it will take a few seconds into that.While Visual Basic is user-friendly, it has limitations in terms of space and memory required .

Should I download Microsoft Visual Basic?

If you are a beginner who wants to enter the field of coding, then you should download Microsoft Visual Basic. The object-driven development environment is designed to make coding a simple experience. This includes automatic error checking, the option to launch applications immediately, as well as access to a database of objects and constructions. For professionals, it is a great language that can help quickly code for developing Windows app prototyping.

So the next thing is we have to set the request header using set the request heat out and you have to specify the key value, content type and application types. And so we have to specify that as an argument here, type application. So because we are going to get the response in the form of JSON our next line, we have to send this get re. Well, so you can use a send here. So after that it will take a few seconds into that.

So let’s print this and see whether it actually makes an API call or not. Let me put a debug and make sure that response make sure that we are getting response or not. So stay this file and run this grip. So we got the response and it successfully make a get request. Now it’s time to pass it, because this STF response, if you look, add this so response Jetta type.

It is a string, so it’s very hard to pass. And that’s why we use this third party JSON converter. And let’s see how to parse the object which is presently inside this JSON response. Let me comment this one.