当前位置：文江博客话题详情

从 Excel 中执行 HTTP Post 并解析结果

发布于 2024-08-12 16:56:58 字数 275 浏览 9 评论 0原文

我可以访问 API。该 API 将 XML post 作为输入，然后返回包含相关数据的 XML 响应。

我想

将 HTTP Post 发送到服务器（身份验证和请求将一起发送）
接收响应（返回的选项之一是 CSV 或 XML）
将数据插入到适当的行和列中，然后使用执行数据分析数据透视表。

我没有 Excel 编程背景，但熟悉不同的 Web 脚本语言、HTML、CSS、Javascript 等。

有什么想法吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

很快妥协 2024-08-19 16:56:58

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

The Excel request side can be handled with this VBA code.

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

回复收藏 0 原文

霓裳挽歌倾城醉 2024-08-19 16:56:58

如果您需要将输入 xml 作为消息正文发送，请按此处操作。
您可能需要添加更多或更改请求标头才能使其为您工作。

使用 DOMDocument 对象可以轻松处理 xml 文档。

添加项目引用；

Microsoft WinHTTP 服务，版本 5.1
Microsoft XML，v6.0

示例：

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

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.

Using the DOMDocument object make it easy to work with your xml documents.

Add a project references to;

Microsoft WinHTTP Services, version 5.1
Microsoft 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

回复收藏 0 原文

坚持沉默 2024-08-19 16:56:58

这就是我最终使用的：

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "POST", urlPath, False
objHTTP.setRequestHeader "Content-Type", "text/xml"
objHTTP.send (request)

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)

回复收藏 0 原文

活雷疯 2024-08-19 16:56:58

我建议在需要 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 ("")

参考： https://github.com/VBA-tools/ VBA-Web/issues/15

I suggest to use WinHttp.WinHttpRequest.5.1 instead of MSXML2.XMLHTTP whenever you need windows authentication, because it allows you to use current user credential to login. Here is an example

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

Reference: https://github.com/VBA-tools/VBA-Web/issues/15

回复收藏 0 原文

~没有更多了~