How can I send an HTTP POST request to a server from Excel using VBA?
ExcelVbaHttpPostServerxmlhttpExcel Problem Overview
What VBA code is required to perform an HTTP POST from an Excel spreadsheet?
Excel Solutions
Solution 1 - Excel
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://www.somedomain.com"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ""
Alternatively, for greater control over the HTTP request you can use WinHttp.WinHttpRequest.5.1
in place of MSXML2.ServerXMLHTTP
.
Solution 2 - Excel
If you need it to work on both Mac and Windows, you can use QueryTables:
With ActiveSheet.QueryTables.Add(Connection:="URL;http://carbon.brighterplanet.com/flights.txt", Destination:=Range("A2"))
.PostText = "origin_airport=MSN&destination_airport=ORD"
.RefreshStyle = xlOverwriteCells
.SaveData = True
.Refresh
End With
Notes:
- Regarding output... I don't know if it's possible to return the results to the same cell that called the VBA function. In the example above, the result is written into A2.
- Regarding input... If you want the results to refresh when you change certain cells, make sure those cells are the argument to your VBA function.
- This won't work on Excel for Mac 2008, which doesn't have VBA. Excel for Mac 2011 got VBA back.
For more details, you can see my full summary about "using web services from Excel."
Solution 3 - Excel
In addition to the answer of Bill the Lizard:
Most of the backends parse the raw post data. In PHP for example, you will have an array $_POST
in which individual variables within the post data will be stored. In this case you have to use an additional header "Content-type: application/x-www-form-urlencoded"
:
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "http://www.somedomain.com"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send "var1=value1&var2=value2&var3=value3"
Otherwise, you have to read the raw post data on the variable "$HTTP_RAW_POST_DATA"
.
Solution 4 - Excel
To complete the response of the other users:
For this I have created an "WinHttp.WinHttpRequest.5.1" object.
Send a post request with some data from Excel using VBA:
Dim LoginRequest As Object
Set LoginRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
LoginRequest.Open "POST", "http://...", False
LoginRequest.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
LoginRequest.send ("key1=value1&key2=value2")
Send a get request with token authentication from Excel using VBA:
Dim TCRequestItem As Object
Set TCRequestItem = CreateObject("WinHttp.WinHttpRequest.5.1")
TCRequestItem.Open "GET", "http://...", False
TCRequestItem.setRequestHeader "Content-Type", "application/xml"
TCRequestItem.setRequestHeader "Accept", "application/xml"
TCRequestItem.setRequestHeader "Authorization", "Bearer " & token
TCRequestItem.send
Solution 5 - Excel
You can use ServerXMLHTTP
in a VBA project by adding a reference to MSXML
.
> 1. Open the VBA Editor (usually by editing a Macro) > 2. Go to the list of Available References > 3. Check Microsoft XML > 4. Click OK.
(from Referencing MSXML within VBA Projects)
The ServerXMLHTTP MSDN documentation has full details about all the properties and methods of ServerXMLHTTP.
In short though, it works basically like this:
> 1. Call open method to connect to the remote server > 2. Call send to send the request. > 3. Read the response via responseXML, responseText, responseStream or responseBody
Solution 6 - Excel
I did this before using the MSXML library and then using the XMLHttpRequest object, see here.