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?

score 173 · Accepted Answer · edited Sep 08 '21 at 20:50

173

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.

edited Sep 08 '21 at 20:50

MarredCheese

17,541
8
92
91

answered Oct 01 '08 at 17:03

Bill the Lizard

398,270
210
566
880

10

For greater control over the HTTP request you can use "WinHttp.WinHttpRequest.5.1" instead of "MSXML2.ServerXMLHTTP" – Matthew Murdoch Oct 03 '08 at 10:23
6

Noteworthy is that you can also use this to issue a HTTP PUT by changing "POST" to "PUT". Content to PUT goes in the .send() method. Any additional headers you need to set can be done also following syntax used in the User-Agent example. – radicand Jan 24 '12 at 04:47
10

Please don't use parentheses around parameters if you do not use the Sub's return value: VBA Syntax do not allow parentheses around Sub's parameters (they are needed for Functions, though), so these parentheses are actually arithmetic parentheses used to clarify operator precedence. Apart from being misleading and unclear, this can eventually result in a runtime error if the argument is an object. And although not explicitly asked for, usually you would want to use the HTTP response, which you could mention can be retrieved by `objHTTP.responseText`. – Leviathan Jan 09 '17 at 20:08
6

@Leviathan the parens actually do more than that: they make the VBA runtime *evaluate the expression as a value*, and *pass it to the method ByVal* **regardless** of whether the method's signature says `ByRef` or not. That's why using them with object variables of a type that doesn't have a default member causes run-time errors; and using them on an object that *does* have a default member, passes that default member's value instead of the actual object. – Mathieu Guindon Oct 11 '17 at 20:25
2

how about a json payload ...how can we pass that in send ? – Amrit Jul 22 '19 at 13:15
The suggestion to use WinHttp.WinHttpRequest.5.1 saved my bacon, as the MSXML classes wouldn't allow me to retrieve more than one Set-Cookie header from a server response to my request. Thanks! – Ben Seymour Jan 13 '20 at 14:00
1

A million times thank you. I don't know why, because I didn't see it in any other examples, but the "User-Agent" header was crucial for me, because otherwise the body wasn't being sent in my request. – redOctober13 Mar 20 '20 at 19:48

score 60 · Answer 2 · edited Sep 08 '21 at 20:48

60

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".

edited Sep 08 '21 at 20:48

MarredCheese

17,541
8
92
91

answered Jul 10 '13 at 12:03

thiscode

656
5
5

1

I am trying to post this request (with curly braces) and get compilation errors ...can u pls help: "{"request":{"carName":"Honda","model" : "1A5"}}" – fiddle Feb 06 '18 at 07:05
@fiddle did you URL encode that string? The curly braces are less of a concern than the quotes (which would terminate the strings in the VBA") – Ruscal Aug 12 '20 at 18:18

score 59 · Answer 3 · answered Jan 06 '11 at 17:07

59

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."

answered Jan 06 '11 at 17:07

Seamus Abshere

8,326
4
44
61

4

+1: I only need it on Windows but a cross-platform solution might benefit somebody else. – Matthew Murdoch Jan 07 '11 at 14:27
I don't think you can actually access the html code, you can only get the information on the rendered web page (not the actual html code) – user1493046 May 01 '13 at 13:36
1

+1 for the cross-platform solution and +1 (if I could) for the full summary with gist link and all. Thanks!! – airstrike Jul 22 '13 at 23:36
I had to support both windows and mac, and this solution was right on! Note that when you specify PostText, the URL should process POST requests, otherwise it should process GET requests. – amolk Oct 24 '13 at 22:15
2

Is it possible to output the results to a variable instead of a Range ? Need to do some Json parsing. – Standaa - Remember Monica Jul 13 '16 at 16:12

score 15 · Answer 4 · answered Dec 11 '19 at 11:30

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

David, once you send the request, how do you read the response? — ps0604, Feb 16 '20 at 15:52
Response is inside `TCRequestItem Object`, you can read it like: `TCRequestItem.ResponseText` after doing `TCRequestItem.send` — dvque, Feb 18 '20 at 14:50
Please, @david.q, from where did come that "token" variable? — Yorga Babuscan, Feb 26 '22 at 00:42

score 8 · Answer 5 · edited Aug 21 '19 at 09:35

8

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

edited Aug 21 '19 at 09:35

answered Oct 01 '08 at 17:01

Mark Biek

146,731
54
156
201

1

that link uses jscript, not VBA – John Henckel Aug 19 '16 at 04:16
1

Thanks @JohnHenckel. I've made some changes to bring that answer up to date. – Mark Biek Aug 19 '16 at 11:39

score 1 · Answer 6 · edited Apr 18 '20 at 20:12

1

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

edited Apr 18 '20 at 20:12

zonksoft

2,400
1
24
36

answered Oct 01 '08 at 17:03

Sijin

4,530
21
22

1

File not found. – SuShuang Sep 13 '19 at 09:18

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

6 Answers6

Linked