Excel Vba Http Post Request Json Object Using Vba

Excel VBA HTTP Post Request JSON Object

To make an HTTP Post request with JSON data using VBA in Excel, you can leverage the MSXML2.XMLHTTP or WinHttp.WinHttpRequest object. Here’s an example:

Sub PostRequest()
    Dim url As String
    Dim data As String
    Dim httpRequest As Object
    
    ' Define the URL of the API endpoint
    url = "https://api.example.com/endpoint"
    
    ' Define the JSON data to be sent in the request
    data = "{""key1"": ""value1"", ""key2"": ""value2""}"
    
    ' Create a new XMLHTTP or WinHttpRequest object
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    'Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    ' Set the request URL
    httpRequest.Open "POST", url, False
    
    ' Set the request headers
    httpRequest.setRequestHeader "Content-Type", "application/json"
    
    ' Send the request with the JSON data
    httpRequest.send data
    
    ' Get the response from the server
    Dim response As String
    response = httpRequest.responseText
    
    ' Output the response
    MsgBox response
End Sub

In the above example, the URL variable holds the endpoint URL where the request is to be sent. The data variable holds the JSON payload to be included in the request.

To create the HTTP request object, you can use either the MSXML2.XMLHTTP or WinHttp.WinHttpRequest object. Simply uncomment the appropriate line of code based on the object you prefer to use. Both objects provide similar functionality, but the WinHttpRequest object supports more advanced features.

The “Open” method of the request object is used to set the HTTP method and URL. In this case, we’re using “POST” to indicate a POST request.

The “setRequestHeader” method is used to set the “Content-Type” header to specify that the request body is in JSON format.

The “send” method is used to actually send the request with the JSON data as the request body.

The “responseText” property of the request object contains the response from the server. In this example, we’re storing it in the response variable and displaying it using a message box.