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.