vba 从 Excel 中执行 HTTP Post 并解析结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1820345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Perform HTTP Post from within Excel and Parse Results
提问by Scott
I have access to an API. The API takes an XML post as input and then returns an XML response with the relevant data.
我可以访问 API。API 将 XML 帖子作为输入，然后返回带有相关数据的 XML 响应。
I want to
我想要
- Send the HTTP Post to the Server (Authentication and Request will be sent together)
- Receive the response (One of the options to be returned is CSV or XML)
- Insert the data into the appropriate rows and columns and then perform data analysis using pivot tables.
- 将 HTTP Post 发送到服务器（Authentication 和 Request 将一起发送）
- 接收响应（返回的选项之一是 CSV 或 XML）
- 将数据插入适当的行和列，然后使用数据透视表执行数据分析。
I don't have a programming background in excel but am comfortable with different web scripting languages, HTML, CSS, Javascript etc.
我没有 excel 编程背景，但对不同的 Web 脚本语言、HTML、CSS、Javascript 等很满意。
Any ideas?
有任何想法吗？
回答by Andrew
If you need to send your input xml as the message body here is how you can do it. You may need to add more or change the Request headers to get it to work for you.
如果您需要将您的输入 xml 作为消息正文发送，您可以这样做。您可能需要添加更多或更改请求标头以使其适合您。
Using the DOMDocument object make it easy to work with your xml documents.
使用 DOMDocument 对象可以轻松处理您的 xml 文档。
Add a project references to;
添加项目引用；
- Microsoft WinHTTP Services, version 5.1
- Microsoft XML, v6.0
- Microsoft WinHTTP 服务，版本 5.1
- 微软 XML，v6.0
Example:
例子：
Dim xmlInput As String
xmlInput = "<YourXmlRequest></YourXmlPayload>"
Dim oXmlHttp As MSXML2.XMLHTTP60
Set oXmlHttp = New MSXML2.XMLHTTP60
oXmlHttp.Open "POST", serviceURL, False, "UserName", "Password"
oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
oXmlHttp.setRequestHeader "Connection", "Keep-Alive"
oXmlHttp.setRequestHeader "Accept-Language", "en"
oXmlHttp.send xmlInput
Debug.Print oXmlHttp.responseText
Dim oXmlReturn As MSXML2.DOMDocument60
Set oXmlReturn = New MSXML2.DOMDocument60
oXmlReturn.loadXML oXmlHttp.responseText
回答by Robert Mearns
The Excel request side can be handled with this VBA code.
Excel 请求端可以使用此 VBA 代码进行处理。
Sub GetStuff()
Dim objXML As Object
Dim strData As String
Dim strResponse As String
strData = "Request"
Set objXML = CreateObject("MSXML2.XMLHTTP")
objXML.Open "POST", "www.example.com/api?" & strData, False
objXML.Send
strResponse = objXML.responsetext
MsgBox strResponse
End Sub
回答by Scott
This is what I ended up using:
这是我最终使用的：
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "POST", urlPath, False
objHTTP.setRequestHeader "Content-Type", "text/xml"
objHTTP.send (request)
回答by Naigel
I suggest to use WinHttp.WinHttpRequest.5.1instead of MSXML2.XMLHTTPwhenever you need windows authentication, because it allows you to use current user credential to login. Here is an example
我建议在需要 Windows 身份验证时使用WinHttp.WinHttpRequest.5.1而不是使用MSXML2.XMLHTTP，因为它允许您使用当前用户凭据登录。这是一个例子
Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
http.SetAutoLogonPolicy 0
http.Open "POST", "http://myUrl.html?param1=value1", False
http.setRequestHeader "Content-Type", "text/json"
http.setRequestHeader "User-Agent", "Mozilla/5.0 (iPad; U; CPU OS 3_2_1 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Mobile/7B405"
http.send ("")
