使用Excel VBA调用Azure DevOps REST API时的授权异常问题

问题诊断 #

返回登录页面HTML的核心原因是VBA请求未正确传递身份验证信息，或者自动跟随了Azure DevOps的登录重定向，导致未能获取API的有效响应。

修复方案 #

1. 正确配置Basic Auth授权头 #

Azure DevOps的PAT需通过Basic Auth方式传递：将空字符串:PAT组合后进行Base64编码，再作为Authorization请求头的值，前缀固定为Basic。

2. 禁用自动重定向 #

VBA的XMLHTTP对象默认会自动跟随重定向，需手动关闭该选项，避免被导向登录页面。

3. 修正后的完整VBA代码 #

Option Explicit

' 需先导入JsonConverter.bas模块
Sub FetchAzureDevOpsData()
    Dim xmlHttp As Object
    Dim apiUrl As String
    Dim pat As String
    Dim authHeader As String
    Dim responseText As String
    
    ' 替换为你的实际配置
    apiUrl = "https://dev.azure.com/[你的组织名]/[你的项目名]/_apis/wit/workitems?ids=123&api-version=7.1-preview.3"
    pat = "你的PAT令牌"
    
    ' 构建Basic Auth授权头
    authHeader = "Basic " & EncodeBase64(":" & pat)
    
    ' 使用高版本XMLHTTP对象
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    
    With xmlHttp
        .Open "GET", apiUrl, False
        .SetRequestHeader "Authorization", authHeader
        .SetRequestHeader "Content-Type", "application/json"
        .Option(6) = False ' 禁用自动重定向
        .Send
        
        responseText = .responseText
        
        If .Status = 200 Then
            ' 解析JSON响应
            Dim jsonObj As Object
            Set jsonObj = JsonConverter.ParseJson(responseText)
            
            ' 示例：输出第一个工作项标题
            Debug.Print jsonObj("value")(1)("fields")("System.Title")
        Else
            Debug.Print "请求失败，状态码：" & .Status & vbCrLf & responseText
        End If
    End With
    
    Set xmlHttp = Nothing
End Sub

' Base64编码辅助函数
Function EncodeBase64(inputString As String) As String
    Dim bytes() As Byte
    bytes = StrConv(inputString, vbFromUnicode)
    
    Dim objXML As Object, objNode As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")
    
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = bytes
    
    EncodeBase64 = objNode.Text
    
    Set objNode = Nothing
    Set objXML = Nothing
End Function

4. 关键检查项 #

• 确保使用MSXML2.XMLHTTP.6.0对象，避免旧版本兼容性问题
• 验证PAT的权限范围：比如获取工作项数据需勾选Work Items (Read)权限
• 核对API URL的组织名、项目名拼写，确保与Azure DevOps后台一致
• 测试Base64编码结果：可手动验证":" & PAT的编码值是否与Postman中使用的一致

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