我们如何使用 Excel 宏 (vba) 中的 Restful API？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38246789/
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
How do we use restful APIs from Excel macros (vba)?
提问by AndroidMechanic - Viral Patel
Is there a plugin or library that could be used to access restful APIs from excel (probably using macros) and then store the responses somewhere (probably in a sheet).
是否有插件或库可用于从 excel 访问restful API（可能使用宏），然后将响应存储在某处（可能在工作表中）。
Pardon the missing sample code. I'm not a VBA programmer.
请原谅缺少的示例代码。我不是 VBA 程序员。
回答by Robin Mackenzie
You can use the MSXML library within VBA. Then you can create an XMlHTTP request and do a GET or POST etc. Here's a code sample below. It uses late binding i.e. no need to reference the library first:
您可以在 VBA 中使用MSXML库。然后您可以创建一个 XMlHTTP 请求并执行 GET 或 POST 等操作。这是下面的代码示例。它使用后期绑定，即无需先引用库：
Option Explicit
Sub Test_LateBinding()
Dim objRequest As Object
Dim strUrl As String
Dim blnAsync As Boolean
Dim strResponse As String
Set objRequest = CreateObject("MSXML2.XMLHTTP")
strUrl = "https://jsonplaceholder.typicode.com/posts/1"
blnAsync = True
With objRequest
.Open "GET", strUrl, blnAsync
.SetRequestHeader "Content-Type", "application/json"
.Send
'spin wheels whilst waiting for response
While objRequest.readyState <> 4
DoEvents
Wend
strResponse = .ResponseText
End With
Debug.Print strResponse
End Sub
I'm using this testing website - JSONPlaceholder- to call a RESTful API. This is the response:
我正在使用这个测试网站 - JSONPlaceholder- 来调用 RESTful API。这是回应：
Note that I found that calls to this website with this method fail if you a) make a synchronous request, or b) use httpnot https.
请注意，我发现如果您 a) 发出同步请求，或 b) 使用httpnot ，则使用此方法调用此网站会失败https。
回答by David Leal
Here, you can find a detailed example using REST API for getting the information from JIRA and loading into excel worksheet. The excel file with the macros can be downloaded from the post. Anyway, it includes a detail explanation on each step in case you want to do it your self for your specific purpose. It's not worth repeating here. I was looking for a similar solution and this is so far, the only post I have found that covers from end to end this problem.
在这里，您可以找到使用 REST API 从 JIRA 获取信息并加载到 excel 工作表中的详细示例。可以从帖子中下载带有宏的 excel 文件。无论如何，它包含每个步骤的详细说明，以防您想出于特定目的自行完成。这里不值得重复。我一直在寻找类似的解决方案，这是到目前为止，我发现的唯一一篇从头到尾涵盖这个问题的帖子。
