我们如何使用Excel宏（vba）中的restful API？

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程序员。

1 个解决方案

#1

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 http not https.

请注意,如果您a)发出同步请求,或者b)使用http而不是https,我发现使用此方法调用此网站会失败。

#1