How can I send an HTTP POST request to a server from Excel using VBA?
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
.
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."