How Can I Send An HTTP POST Request To A Server From Excel Using ...

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Companies
   6. Labs
   7. Jobs
   8. Discussions
   9. Collectives

   10. Communities for your favorite technologies. Explore all Collectives

2. Teams

   Ask questions, find answers and collaborate at work with Stack Overflow for Teams.

   Explore Teams Create a free Team
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Get early access and see previews of new features.

Learn more about Labs How can I send an HTTP POST request to a server from Excel using VBA? Ask Question Asked 15 years, 9 months ago Modified 2 years, 5 months ago Viewed 375k times 168

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

• excel
• vba
• http
• post
• serverxmlhttp

Share Improve this question Follow edited Jul 5, 2019 at 20:30 pgSystemTester 9,69422 gold badges2525 silver badges5555 bronze badges asked Oct 1, 2008 at 16:59 Matthew MurdochMatthew Murdoch 31.1k3131 gold badges9797 silver badges127127 bronze badges Add a comment |

6 Answers 6

Sorted by: Reset to default Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 176 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.

Share Improve this answer Follow edited Sep 8, 2021 at 20:50 MarredCheese 19.7k88 gold badges102102 silver badges9898 bronze badges answered Oct 1, 2008 at 17:03 Bill the LizardBill the Lizard 403k210210 gold badges570570 silver badges886886 bronze badges 7

• 10 For greater control over the HTTP request you can use "WinHttp.WinHttpRequest.5.1" instead of "MSXML2.ServerXMLHTTP" – Matthew Murdoch Commented Oct 3, 2008 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 Commented Jan 24, 2012 at 4: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 Commented Jan 9, 2017 at 20:08
• 7 @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 Commented Oct 11, 2017 at 20:25
• 2 how about a json payload ...how can we pass that in send ? – Amrit Commented Jul 22, 2019 at 13:15

| Show 2 more comments 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".

Share Improve this answer Follow edited Sep 8, 2021 at 20:48 MarredCheese 19.7k88 gold badges102102 silver badges9898 bronze badges answered Jul 10, 2013 at 12:03 thiscodethiscode 66655 silver badges55 bronze badges 2

• 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 Commented Feb 6, 2018 at 7: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 Commented Aug 12, 2020 at 18:18

Add a comment | 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."

Share Improve this answer Follow answered Jan 6, 2011 at 17:07 Seamus AbshereSeamus Abshere 8,46644 gold badges4646 silver badges6161 bronze badges 5

• 4 +1: I only need it on Windows but a cross-platform solution might benefit somebody else. – Matthew Murdoch Commented Jan 7, 2011 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 Commented May 1, 2013 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 Commented Jul 22, 2013 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 Commented Oct 24, 2013 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 Commented Jul 13, 2016 at 16:12

Add a comment | 16

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 Share Improve this answer Follow answered Dec 11, 2019 at 11:30 dvquedvque 48944 silver badges1313 bronze badges 3

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

Add a comment | 8

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

Share Improve this answer Follow edited Aug 21, 2019 at 9:35 user9556248 answered Oct 1, 2008 at 17:01 Mark BiekMark Biek 149k5454 gold badges157157 silver badges201201 bronze badges 2

• 1 that link uses jscript, not VBA – John Henckel Commented Aug 19, 2016 at 4:16
• 1 Thanks @JohnHenckel. I've made some changes to bring that answer up to date. – Mark Biek Commented Aug 19, 2016 at 11:39

Add a comment | 1

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

Share Improve this answer Follow edited Apr 18, 2020 at 20:12 zonksoft 2,41911 gold badge2525 silver badges3838 bronze badges answered Oct 1, 2008 at 17:03 SijinSijin 4,5502222 silver badges2222 bronze badges 1

• 1 File not found. – SuShuang Commented Sep 13, 2019 at 9:18

Add a comment | Highly active question. Earn 10 reputation (not counting the association bonus) in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.

Not the answer you're looking for? Browse other questions tagged

• excel
• vba
• http
• post
• serverxmlhttp

or ask your own question.

• Featured on Meta
• We spent a sprint addressing your requests — here’s how it went
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network...
• What makes a homepage useful for logged-in users
• The [lib] tag is being burninated

Linked

19 Getting "method not valid without suitable object" error when trying to make a HTTP request in VBA? 10 How to send files via HTTP_POST with Excel using VBA? 13 getHTTP with (Excel) VBA? 8 How can I retrieve JSON from an HTTP endpoint, from within Excel on MacOS, and parse it? 2 Perform hidden http-request from vba 5 Form.Submit does not go through when using VBA 3 Sending and receiving data via POST in Microsoft Word 2011 for OSX 4 How can I use an Excel formula (no VBA) to retrieve a piece of data within an HTTP POST response? 3 WinHttpRequest on Mac OSX Excel 2011 VBA 3 VBA/Corel Draw: How to send binary and text file in HTTP POST request to server from VBA/VB6 script running from Corel Draw 12/X4? See more linked questions

Related

13 getHTTP with (Excel) VBA? 11 HTTPS POST request using VBA for Excel 11 Perform HTTP Post from within Excel and Parse Results 8 Http Post in Vba 1 Sending data from excel to Server using HTTP Post 10 How to send files via HTTP_POST with Excel using VBA? 0 How to send a post request via excel to a RESTful Web Service without using XML? 7 Pass Parameters in VBA HTTP Post Request 1 Excel 2010 VBA - XMLHTTP 1 How to send a form-data POST request with VBA in Excel using WinHTTPRequest

Hot Network Questions

• When selling a machine with proprietary software that links against an LGPLv3 library, do I need to give the customer root access?
• Do you always experience the gravitational influence of other mass as you see them in your frame?
• I forgot to remove all authors' names from the appendix for a double-blind journal submission. What are the potential consequences?
• Can the differential be unitless while the variable have an unit in integration?
• Mathematics & Logic (Boolean Algebra)
• How to photograph the lettering on a bronze plaque?
• How to pin to the Task Bar the Device Manager on Windows 11?
• Dual citizenship with USA & South Africa and exited South Africa on wrong passport (USA). What passport do I use to reenter SA?
• 11 trees in 6 rows with 4 trees in each row
• What is the correct translation of the ending of 2 Peter 3:17?
• Could mathematical truths have been otherwise?
• How does a country without exit immigration check know you have overstayed?
• How can I power both sides of breaker box with two 120 volt battery backups?
• Is a desert planet with a small habitable area possible?
• Is it possible for some p-values to be impossible? (because statistic generated by parametric bootstrap is mostly the same value.)
• Is the text of a LLM determined by a random seed?
• Mac Mini G4 not reinitialized
• Transhumans, posthumans, and AI attacked by baseline humans
• How much does a factory reset help in hiding a device's identification details?
• Question about NMAP HTTP Verb Tampering
• Why do Electric Aircraft Seem to Eschew Photovoltaics?
• Optimizing Pi Estimation Code
• 'adb help' for 'root' says "restart adbd with root permissions", but 'adb root' says "adbd cannot run as root in production builds"
• A manifold whose tangent space of a sum of line bundles and higher rank vector bundles

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb