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.
Read More Articles
- How would I use VBA to automatically populate an excel worksheet with data from the pivot table of a separate excel workbook?
- How can I get og:image from resource with VBA excel
- How to export the data from excel to word as text use VBA
- how to get data from password protected and closed workbook with in excel 2007
- How to use VBA or Powershell to export lists from Sharepoint server with NTLM Authentication to Excel
- How to remove subtotal from a excel pivot-table with mutiple data fields useing vba
- Excel VBA - How to get data from multiple-array JSON into columns
- How to import data from Google Sheets into Excel with VBA
- Get data from website screen to Excel with form - VBA
- How to get data from an already opened excel workbook with a Word macro?
- How do you use VLOOKUP with a loop in Excel VBA to write data into multiple sheets?
- How to use a sql query and get data from one Excel sheet to another in VBA?
- how to count the number of rows with data from a specific cell in excel vba
- Using VBA how do I load an API DataSet into Excel from an HTTP GET function?
- I want to get data from web site with Excel vba but I can't
- How to get content from MS word cells with VBA and clean it for Excel
- How to get text and select text from a list box in another application using VBA with the help of win32 API
- How to get a table data from a website which use POST method with VB Excel?
- How to pull data from a website with Excel VBA
- VBA - how to get data from excel file just downloaded into your current excel file
- Visual Studio VBA Excel add-in get data from stored procedure with x number of columns
- How to get multiple values with a single look up in Excel with transposing the data from column to rows
- How to get multiple values with a single look up in Excel with transposing the data from column to rows
- Get Data From Access Database with Excel VBA
- get data from a JSON string with VBA Excel
- How to get Google Places API (nearby search) data to Excel VBA
- How to get a range of cells in excel vba taking data from other sheet?
- Excel VBA with XML V6.0 how do I get the data out of the file
- How to insert (flexible) data into S/4HANA from cells in an Excel file with VBA code?
- How can I get selected data from a combobox on a website into Excel with Selenium VBA?
- CDate type mismatch error
- How to copy URL from specific cell using VBA
- Need If statement to check range of cells
- Using a counter in a loop within a loop = problems in excel vba
- Excel vba remove tooltip if cell blank
- Runtime error 3075 can't figure it out
- How do you disable "Save and send" in Excel 2010 (in the File ribbon (called backstage in Office 2010)?
- How to show value in cell depending on its filled color - event in Excel VBA
- Why can't I delete a sheet after copying it?
- Application.worksheetFunction.match not working correctly VBA Excel
- Excel VBA date copying different for no apparent reason?
- Excel VBA VLOOKUP doesn't return Decimal values
- Operator Overloading in VBA
- How to create a loop to add data series to a chart?
- Copy Record From One Form to Another
- Color field in access 2003
- Copy and Rename Excel ActiveSheet in vba
- Loop through folders and merge excel files - 1 Excel File, Multiple Worksheets
- IsEmpty functionality issue
- Formula to eliminate all but alpha characters