VBA调用带API Key认证的REST API报‘API key缺失’求助

背景 #

• 需调用的REST API提供了URL、API Key（token）、API Key标识为“apikey”，以及用户名
• Postman中通过API Key认证成功调用：在Authorization选项卡选择API Key类型，输入apikey作为键，对应token作为值，无需使用用户名
• 现有VBA自动化架构，需直接用VBA实现API调用，不集成Postman

尝试的VBA代码及问题 #

第一段代码（Basic Auth方式） #

Public Sub GetResults()
    Dim data As String, json As Object '<  VBE > Tools > References > Microsoft Scripting Runtime
    Dim strUrl As String
    strUrl = "[Provided URL]"

    Set hReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    With hReq
        .Open "GET", strUrl, False
        .setRequestHeader "Authorization", "Basic " + _
            EncodeBase64("[Username]" + ":" + "[Token]")
        .send
    End With

    Dim response As String
    response = hReq.responseText
    
    MsgBox response
End Sub

Function EncodeBase64(text As String) As String
    Dim arrData() As Byte
    arrData = StrConv(text, vbFromUnicode)

    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement

    Set objXML = New MSXML2.DOMDocument60
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = Application.Clean(objNode.text)

    Set objNode = Nothing
    Set objXML = Nothing
End Function

尝试将[Username]替换为实际用户名或apikey，均收到API key is missing错误，说明请求已到达服务器，但认证头设置不符合要求。

第二段代码（Bearer Auth方式） #

Sub GetResultData()
    Dim hReq As Object, json As Dictionary
    Dim sht As Worksheet

    Set sht = Sheet1

    Dim strUrl As String
    strUrl = "[Provided URL]"
    Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .setRequestHeader "Authorization", "Bearer [Username]:[Token]"
        .send
    End With

    Dim response As String
    response = hReq.responseText
    
    MsgBox response
End Sub

同样收到API key is missing错误，不确定是否需要Base64编码。

问题根源及解决方案 #

核心错误是误用了认证方式：Postman中使用的是API Key认证，而非Basic Auth或Bearer Auth。这种认证方式的本质是直接在请求头中添加一个名为apikey的字段，值为你的token，不需要放在Authorization头里。

修正后的VBA代码示例（WinHttp版本） #

Public Sub GetResults_Correct()
    Dim strUrl As String
    strUrl = "[Provided URL]"
    Dim apiToken As String
    apiToken = "[Your Token]" ' 替换为实际token

    Set hReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    With hReq
        .Open "GET", strUrl, False
        .setRequestHeader "apikey", apiToken ' 直接添加apikey请求头
        .send
    End With

    Dim response As String
    response = hReq.responseText
    
    MsgBox response
End Sub

特殊情况处理 #

如果API要求将API Key放在Authorization头中（比如格式为apikey <token>），则修改请求头为：

.setRequestHeader "Authorization", "apikey " & apiToken

验证建议：在Postman中查看请求的Raw Headers，确认实际发送的请求头格式，然后在VBA中完全复刻即可。

内容的提问来源于stack exchange，提问作者CrombopulousSqueeze