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

问题描述

是否有插件或库可用于从 excel 访问 Restful API(可能使用宏)，然后将响应存储在某处(可能在工作表中).

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

解决方案

您可以使用

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

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).

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

解决方案

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

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

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) 中的 Restful API?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！