How use VBA to get data from API with bearer token to Excel

Hello developers, In this post, we are going to look at How can we call API using Excel VBA API token authentication. 

In this article, we will learn to rest API Using Excel VBA HTTP request 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"":""eve.holt@reqres.in"",""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.

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.

The post How use VBA to get data from API with bearer token to Excel appeared first on Software Development | Programming Tutorials.