Hello developers, In this post, we are going to look at How can we send an HTTP POST request to a server from Excel VBA?.
In this article, we will learn to call post API Using Excel VBA code to send a POST and retrieve data from the server .
You can download the HTTP POST request source code at the bottom of the post.
Api end
We are going to cover the below point in this article
- Using Rest API Sending JSON object as a POST request
- Send a POST and retrieve data Using Excel VBA code
Let’s understand step-by-step to Send JSON data in POST request VBA in Excel VBA with help of the rest API.
As we know that Excel doesn’t have a built JSON parser for parsing JSON strings. I’m using VBA-tools JSON parser that helps us to parse the JSON response that we return after making a post request.
with the help of VBA-tools JSON, we can easily parse the object and get a value desire value.
go to the Git link: https://github.com/VBA-tools/VBA-JSON and download the source code.
Extract that folder and Go to File and click on Import file in VBA IDE and select the JsonConverter.bas click on Open.
add reference of Microsoft scripting runtime references
Now all setup done for parsing the api response
VBA POST request with parameters Example -1
And this is a simple registration rest API that accepts email and password as JSON parameters and after SUCCESSFUL call return Userid and token id dummy data of user object.
Api End point: https://reqres.in/api/register
Request
{ "email": "test@gmail.com", "password": "test@123" }
Response(200)
{ "id": 4, "token": "3434Q54454p565655tke4Pnpja7X4" }
Vba code for calling Api
Private Sub CommandButton3_Click() Dim objpostHTTP As Object Dim JsonBody As String Dim Jsonresult As Object JsonBody = "{""email"":""eve.holt@reqres.in"",""password"":""vba1234""}" 'here I am pulling creating json body Dim apiresult As String Set objpostHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "https://reqres.in/api/register" objpostHTTP.Open "POST", URL, False objpostHTTP.setRequestHeader "Content-type", "application/json" objpostHTTP.Send (JsonBody) apiresult = objpostHTTP.responseText Set Jsonresult = JsonConverter.ParseJson(apiresult) MsgBox ("User registered with us Id:" & Jsonresult("id") & " TokenId: " & Jsonresult("token")) End Sub
VBA POST request with parameters Example -2
And this is a simple rest API for creating user that accepts “name” and “job” as JSON parameters and after SUCCESSFUL call return user object.
Api End point: https://reqres.in//api/users
Request
{ "name": "Alex bliss", "job": "HR" }
Response(201)
{ "name": "Alex bliss", "job": "HR", "id": "932", "createdAt": "2021-08-26T06:32:37.312Z" }
Vba code for calling Api
Private Sub CommandButton2_Click() Dim objHTTP As Object Dim Json As String Dim Jsonresult As Object Json = "{""name"":""Alex bliss"",""job"":""HR""}" 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") & " UserId:" & Jsonresult("id")) End Sub
What is the utilization of Visual Basic?
Visual Basic.NET, and Visual Basic Applications, Although these systems have acquired ubiquity, Visual Basic is still very gainful.
The third era programming language is planned by designers to help them fabricate Windows applications. It works with a climate in which developers can compose code in .exe or executable documents. They can utilize the language to fabricate an in-house front-end framework for getting to huge information bases. Since the language upholds progressing changes, you can keep coding and altering your work as and when required.
Microsoft Visual Basic download has a few impediments. Assuming you need to make programs that require a great deal of handling time, then, at that point the product isn’t appropriate for you.
Why Use Excel VBA?
There are two different ways to make Excel macros. The principal technique is to utilize the large scale recorder. Subsequent to enacting the recorder, Excel will record every one of the means performed by the client and save it as a “cycle” known as a full scale. At the point when the client ends the recorder, this large scale is saved and can be alloted to a catch which when clicked will run a similar interaction once more. This strategy is somewhat straightforward and requires no underlying information on VBA code. This technique will work for basic cycles.
The ruin of this strategy, notwithstanding, is that it isn’t entirely adjustable, and the large scale will duplicate the client’s feedback precisely. Of course, recorder macros additionally utilize outright references rather than relative references. IKt implies that macros made this way are undeniably challenging to use with factors and “keen” methodology.
Dominate VBA empowers you to utilize English like explanations to compose directions for building different applications. This permits you to expand Excel usefulness with the goal that it acts as you need.
What is VBA utilized for?
VBA is utilized for both individual use just as business use. For business use, you can construct vigorous projects and influence the force of Excel in your custom projects utilizing VBA.
Requirements for Learning VBA Excel Tutorial?
Nothing by any means! This Excel VBA preparing regards you as a total fledgling to VBA. In any case, it is alluring on the off chance that you know the fundamentals of Excel and how to perform works in Excel, it will help your learning pace and comprehension.
Spotless and straightforward interface?
After the establishment is finished, you will be in the realistic improvement climate of Visual Basic. It is basic and deliberate, to such an extent that novices, also as specialists, can access and utilize every one of its capacities with no issues. The interface accompanies an advancement bar, catches, names, just as some altering devices that can assist you with changing the programming module consequently.
While creating Windows 10 applications, you can begin utilizing the accessible controls, components, and structures to construct the UI of the application. The Microsoft Visual Basic download, as other programming dialects, upholds the utilization of normal builds. Furthermore, you can utilize those components and develops when composing code to characterize the conduct of your application.
Nonetheless, since the language has an occasion driven climate, you will see a few contrasts in the develops that you use to assemble your application. Likewise, the language gives you admittance to the information base with the assistance of ActiveX information items and controls. Utilizing the data set, you can make different applications including recreation programs, adding machines, straightforward games and substantially more.
What are the fundamental elements of Virtual Basic?
In contrast to other programming dialects, Microsoft Visual Basic gives fast application improvement to application downloads. It is coordinated with string preparing provisions and C++, MFC and F# usefulness. The framework upholds multi-focusing just as the Windows Presentation Framework, so designers can picture different Windows applications, work area instruments, Metro-style projects, and equipment drivers.
Snap to dispatch
Quite possibly the main components of utilizing Visual Basic to foster projects is that it allows you to make executable records consistently. These documents are not difficult to send out and can be utilized to check the advancement of the application and make changes any place fundamental. Moreover, the program trades XAML code for clients, so they can dispatch an application with the snap of a catch right away.
Appreciate various topics?
Albeit the program is restricted to Microsoft working frameworks just, it gives a scope of Windows topics for software engineers to try different things with. You can assemble programming that is in a state of harmony with the working framework on which you need to run the last application. Since the coordinated advancement climate additionally gives you admittance to the information base, you can drag components from that point and drop them into your code.
The post How send an HTTP POST request to a server from Excel using VBA? appeared first on Software Development | Programming Tutorials.
Read More Articles
- How can I send an HTTP POST request to a server from Excel using VBA?
- VBA/Corel Draw: How to send binary and text file in HTTP POST request to server from VBA/VB6 script running from Corel Draw 12/X4?
- How to send a form-data POST request with VBA in Excel using WinHTTPRequest
- Send data in HTTP Post from VBA Excel for Mac
- Sending data from excel to Server using HTTP Post
- Using VBA how do I load an API DataSet into Excel from an HTTP GET function?
- How to send a post request via excel to a RESTful Web Service without using XML?
- How to update Sql server table from excel data using vba code?
- How to export excel spreadsheet data as JSON HTTP Post request using VBScript
- 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
- Which references to add in HTTP Request from Excel VBA
- How to fetch data from XML file to Excel sheet using VBA
- HTTPS POST request using VBA for Excel
- How to obtain EXIF info from picture in an excel worksheet using VBA
- Using Excel VBA code to send a POST and retrieve data
- VBA send email from Excel using OFT template - Suppress automatic signatures
- How to release inprocess COM Server object from Excel VBA
- How to set Replacement Text to Bullet Format when creating WordDoc from Excel using VBA
- HTTP Get Request Using VBA in OSX Excel
- Connecting to SQL Server over network using ADO from Excel VBA
- How can I GZip compress a file from Excel VBA using code in an .xla file only?
- How to copy using Multiple filter from user inputbox data in Excel VBA
- Send an email from Excel 2007 VBA using an Outlook Template & Set Variables
- Access + VBA + SQL - How to export multiple queries into one excel Workbook, but, multiple Worksheet using the criteria from a table
- How delete a series from an Excel chart using VBA
- Getting "The server's host key is not cached in the registry" when using pscp.exe to transfer file from Windows to Unix server in VBA + Excel
- How to retrieve data from access database(.accdb) into excel using vba
- excel vba http request download data from yahoo finance
- Uploading data from Excel 365 to Azure Sql Server using VBA only
- How to run a code through each column (containing information) within excel?
- How to set solver 'Value of:' to cell reference
- Sheet / Range Select (combined) not working
- Excel VBA - Cell color Change
- VBA: Double for loop through very large amounts of data
- VBA Excel SQL INSERT query adds apostrophe to all data inserted
- VBA How to declare an array with elements with different datatype
- Personal VBA code Workbook - BeforeClose event across all/any open workbook
- Copy all Rows in excel sheet only if specific cell = today's date
- Excel-outlook VBA 2013 not working on 2016 Outlook version
- How do I issue an HTTP GET from Excel VBA for Mac
- VBA store path including Environ("username") in a text file
- Data source start and end range variables
- How to disable read-only attribute for multiple Excel files on sharepoint?
- Where is my ComboBox located
- How to grab data from VBA Access form and use that data in Outlook userform?
- copying cells in Excel based on values in other cells
- How to make a dynamic log in userform in vba
- loop through a column and pull out specific cells
- VBA error '91' - cannot find source of error