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 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 get the current datetime in UTC from an Excel VBA macro
- How to get the background color from a Conditional Formatting in Excel using VBA
- How do I issue an HTTP GET from Excel VBA for Mac
- How can I use VBA to add a Connection (to an External Data Source) in Excel and Save it to that Excel spreadsheet's list of Connections
- How to fetch data from XML file to Excel sheet using VBA
- How to get cell address from Find function in Excel VBA
- How do I run a Stored procedure with parameters from Excel VBA string?
- How to join two recordset created from two different data source in excel vba
- How to retrieve data from Excel with ADODB connection if the first line of the worksheet does not have the column name?
- How to copy using Multiple filter from user inputbox data in Excel VBA
- Get Data into a Powerpoint Graph from Microsoft Excel using VBA
- How to select from excel table with left join in access database - EXCEL VBA
- How to populate data from a range (multiple rows and columns) to listbox with VBA
- How to open a workbook from Excel 16 with VBA on MAC?
- How to retrieve data from access database(.accdb) into excel using vba
- How to export an R Random Forest model for use in Excel VBA without API calls
- Excel VBA - How to copy a 2D matrix content from a 3D matrix with a single command?
- How to use VBA to read the values of a checkbox from an Excel userform
- Scraping data from website with css selectors excel vba
- How to use VLOOKUP and get details from other sheet using VBA
- How to insert a picture into Excel at a specified cell position with python (Anaconda) use vba
- Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?
- How to use INDEX and MATCH in VBA + Excel with multiple criterias?
- How to get cell row from current function VBA Excel
- Writing to current document with a visual studio macro is extremely slow, suggestions to speedup?
- If cell A1 is greater than B1, cut and paste row to first empty row
- How to assign variable to Data Validation Formula1:= in Excel VBA?
- A better VBA editor for Autocad
- Find unknown method or keyword in VBE
- Replace bullets with dashes in a Word document
- How can I automatically send email from Thunderbird with Excel VBA?
- Retrieve nth key in dictionary VBA
- Print values to worksheet with Do While loop rather than using Immediate window
- Append Subject Header in Outlook (VBA)
- Using a .Net DLL in Microsoft Access VBA
- Excel 2010 conditional formatting individual rows
- How to Delete a record in a subform when you click a button
- Inserting data from web to excel sheet - VBA
- copy paste range of cells for every row called yyyx
- Excel macro execution crushing, trying to simplify the formulas
- Lock cells in specific worksheets
- Copy automatically only the color from one cell to another- EXCEL VBA
- Detecting Event on ComboBoxes Added at Runtime on Excel
- Historical Yahoo Finance API On Fritz Again?