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

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

提问by Matthew Murdoch

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

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

回答by Bill the Lizard

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.1in place of MSXML2.ServerXMLHTTP.

或者，在HTTP请求更大的控制权，你可以使用WinHttp.WinHttpRequest.5.1代替MSXML2.ServerXMLHTTP。

回答by Seamus Abshere

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

• 关于输出...我不知道是否可以将结果返回到调用 VBA 函数的同一个单元格。在上面的例子中，结果被写入 A2。
• 关于输入...如果您希望在更改某些单元格时刷新结果，请确保这些单元格是您的 VBA 函数的参数。
• 这不适用于没有 VBA 的 Excel for Mac 2008。Excel for Mac 2011 恢复了 VBA。

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

有关更多详细信息，您可以查看我关于“使用 Excel 中的 Web 服务”的完整摘要。

回答by thiscode

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

大多数后端解析原始帖子数据。例如，在 PHP 中，您将拥有一个数组$_POST，其中将存储帖子数据中的各个变量。在这种情况下，您必须使用额外的标题"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".

否则，您必须读取有关变量的原始发布数据"$HTTP_RAW_POST_DATA"。

回答by Mark Biek

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

您可以ServerXMLHTTP通过添加对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.

1. 打开 VBA 编辑器（通常通过编辑宏）
2. 转到可用参考列表
3. 检查 Microsoft XML
4. 单击确定。

(from Referencing MSXML within VBA Projects)

（来自在 VBA 项目中引用 MSXML）

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

该ServerXMLHTTP的MSDN文档有大约所有属性和ServerXMLHTTP的方法的全部细节。

In short though, it works basically like this:

简而言之，它的工作原理基本上是这样的：

1. Call openmethod to connect to the remote server
2. Call sendto send the request.
3. Read the response via responseXML, responseText, responseStreamor responseBody

1. 调用open方法连接远程服务器
2. 调用send发送请求。
3. 通过responseXML、responseText、responseStream或responseBody读取响应

回答by David Q

To complete the response of the other users:

要完成其他用户的响应：

For this I have created an "WinHttp.WinHttpRequest.5.1"object.

为此，我创建了一个“WinHttp.WinHttpRequest.5.1”对象。

Send a post request with some data from Excel using VBA:

使用 VBA 从 Excel 发送带有一些数据的发布请求：

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:

使用 VBA 从 Excel 发送带有令牌身份验证的 get 请求：

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

回答by Sijin

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

我在使用 MSXML 库然后使用 XMLHttpRequest 对象之前这样做了，请参阅此处。