如何使用VBA从Excel向服务器发送HTTP POST请求?

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

从Excel电子表格执行HTTP POST需要什么VBA代码?

5 个解决方案

#1

121

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.

或者，为了更好地控制HTTP请求，您可以使用WinHttp.WinHttpRequest.5.1代替MSXML2.ServerXMLHTTP。

#2

If you need it to work on both Mac and Windows, you can use QueryTables:

如果您需要它在Mac和Windows上工作，您可以使用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.
关于输出……我不知道是否有可能将结果返回到调用VBA函数的相同单元格。在上面的示例中，结果被写入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.
关于输入…如果希望在更改某些单元格时刷新结果，请确保这些单元格是VBA函数的参数。
This won't work on Excel for Mac 2008, which doesn't have VBA. Excel for Mac 2011 got VBA back.
这对Mac 2008的Excel不适用，因为它没有VBA。Mac 2011版的Excel恢复了VBA。

For more details, you can see my full summary about "using web services from Excel."

有关详细信息，请参阅我关于“使用Excel中的web服务”的完整总结。

#3

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

大多数后端解析原始post数据。例如，在PHP中，将有一个$_POST数组，其中将存储post数据中的单个变量。在这种情况下，您必须使用附加的标题“Content-type: application/x-www-form- urlencodes”:

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

否则，您必须读取变量“$HTTP_RAW_POST_DATA”的原始post数据。

#4

You can use ServerXMLHTTP in a VBA project by adding a reference to MSXML.

通过向MSXML添加引用，您可以在VBA项目中使用ServerXMLHTTP。

Open the VBA Editor (usually by editing a Macro)
打开VBA编辑器(通常通过编辑一个宏)
Go to the list of Available References
转到可用引用列表
Check Microsoft XML
检查微软XML
Click OK.
单击OK。

(from Referencing MSXML within VBA Projects)

(从VBA项目中引用MSXML开始)

The ServerXMLHTTP MSDN documentation has full details about all the properties and methods of ServerXMLHTTP.

ServerXMLHTTP MSDN文档详细介绍了ServerXMLHTTP的所有属性和方法。

In short though, it works basically like this:

总之，它的工作原理是这样的:

Call open method to connect to the remote server
调用open方法连接到远程服务器
Call send to send the request.
调用send发送请求。
Read the response via responseXML, responseText, responseStream or responseBody
通过responseXML、responseText、responseStream或responseBody读取响应

#5

I did this before using the MSXML library and then using the XMLHttpRequest object. See http://scriptorium.serve-it.nl/view.php?sid=40

在使用MSXML库和XMLHttpRequest对象之前，我已经这样做了。参见http://scriptorium.serve-it.nl/view.php?sid=40

#1

121

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("")