Hello everyone. In this post, we are going to look at how to make a rest API call from VBA. That is what we are going to learn in this post. And this is a sample API which going to return this dummy data user object. 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": "janet.weaver@reqres.in", "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.
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. 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.
The post [Solved]-How to call rest api from Excel macros vba and Parse Json appeared first on Software Development | Programming Tutorials.
Read More Articles
- How can I retrieve JSON from an HTTP endpoint, from within Excel on MacOS, and parse it?
- How to read from a text (.txt) file and place data into an excel 2016 document using VBA Macros editor?
- How to Call VBA Function from Excel Cells?
- How do I call VBA code in an Excel spreadsheet from Java?
- How to call Excel VBA functions and subs using Python win32com?
- How to find and call javascript method from vba
- How do I call an Oracle stored procedure from an Excel VBA script?
- How to load vba macros to the excel file from a text file by a C# code?
- How to call an Excel VBA Macro from Java Code?
- Excel VBA - call same macro from both Ribbon and AutoOpen
- Passing a LPCTSTR parameter to an API call from VBA in a PTRSAFE and UNICODE safe manner
- How to call VBA function from excel
- How to instantiate a vba class and call a method from vb.net?
- VBA - How to download .xls from website and put data into excel file
- How to call Word macros from Excel
- How to delete a column of a table and shift the rest to the left without affecting the previous tables in excel vba
- Excel VBA - How to get data from multiple-array JSON into columns
- How to copy column data from one sheet and then copy that to another sheet in vba excel
- How do we use restful APIs from Excel macros (vba)?
- How to call a macro from a button and pass arguments
- How to call .NET methods from Excel VBA?
- How to parse JSON with VBA without external libraries?
- How to import long number from csv to excel without converting to scientific notation in VBA
- How to copy Outlook mail message into excel using VBA or Macros
- 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 to call a Bash script from VBA (Excel)
- How to find text in a column and saving the row number where it is first found - Excel VBA
- how to get the list of Function and Sub of a given module name in Excel VBA
- Trying to run a DELETE query using DAO in VBA
- Loop through excel files in a directory and copy onto master sheet
- Finding an Excel Spreadsheet's template path
- Identifying the current cell in a formula
- Comparing two Sheet objects (not contents)
- Retrieve multiple items in column filter and then delete the rows brought back
- VBA - Create, edit, and hyperlink shape
- VBA Refer to TextBox or Label using a loop
- Sort column into rows of eight cells
- Macro VBA to get selected text in Outlook 2003
- dir function finds xls not xlsx in excel vba
- Format pages to print in 2 columns
- Dynamically Hiding Columns Based On Cell Values
- How to speed up tables transfer between Access and SQL Server using VBA?
- Dropdown list - default value
- Unexpected order for a range sorted with non alpha-numeric characters
- VBA to loop through email attachments and save based on given criteria
- Programmatically group and ungroup rows or columns
- Linking cells to each other; having cells update linked cells and vice versa
- VBA Macro: Formula is Based on a Column that changes locations