How send an HTTP POST request to a server from Excel using VBA?

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

Download Source Code

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.