我们如何使用Excel宏(vba)中的静态API? [英] How do we use restful APIs from Excel macros (vba)?

问题描述

是否有一个插件或库可用于从excel访问宁静的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).

请原谅缺少的示例代码.我不是VBA程序员.

Pardon the missing sample code. I'm not a VBA programmer.

推荐答案

您可以使用 MSXML 库.然后，您可以创建XMlHTTP请求并执行GET或POST等.这是下面的代码示例.它使用后期绑定，即无需先引用库:

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:

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

我正在使用这个测试网站- JSONPlaceholder -调用RESTful API.这是响应:

I'm using this testing website - JSONPlaceholder - to call a RESTful API. This is the response:

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

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.

这篇关于我们如何使用Excel宏(vba)中的静态API?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！