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

Question

What VBA code is required to perform an HTTP POST from an Excel spreadsheet

User · Answer

You can use ServerXMLHTTP in a VBA project by adding a reference to MSXML         Open the VBA Editor  usually by editing a Macro    Go to the list of Available References   Check Microsoft XML   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         Call open method to connect to the remote server   Call send to send the request    Read the response via responseXML  responseText  responseStream or responseBody

User · Answer

In addition to the anwser 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 amp var2 value2 amp var3 value3     Otherwise you have to read the raw post data on the variable   HTTP RAW POST DATA

User · Answer

I did this before using the MSXML library and then using the XMLHttpRequest object  see here

User · Answer

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 amp 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    amp  token TCRequestItem send

User · Answer

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 amp 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

User · Answer

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

[excel] How can I send an HTTP POST request to a server from Excel using VBA?

Examples related to excel

Examples related to vba

Examples related to http

Examples related to post

Examples related to serverxmlhttp