如何通过VBA脚本调用REST API并获取access_token

先修正请求头的关键问题 #

你当前发送的是username=xxx&password=xxx这种表单格式，对应的Content-Type应该是application/x-www-form-urlencoded，而非代码里写的application/json。如果API要求接收JSON格式的请求体，需要把参数改成JSON字符串格式。

获取响应并提取access_token的步骤 #

1. 发送请求后，通过WHTTP.ResponseText获取API返回的JSON字符串
2. 解析JSON字符串，提取access_token字段值
3. 加入请求状态码检查，避免请求失败时解析报错

表单格式请求体的完整代码（适配当前参数格式） #

Dim WHTTP As Object
Set WHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    
mainUrl = "https://googlesample.api.google/okta"
myuser = "myluckyuser"
mypass = "myluckypassword"

' 构造表单格式的请求参数
strAutheticate = "username=" & myuser & "&password=" & mypass
WHTTP.Open "POST", mainUrl, False
' 修正Content-Type为表单格式
WHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
' 如果API需要apikey验证，取消下面注释并替换为实际Token
' WHTTP.setRequestHeader "apikey", "[Token]"
WHTTP.send strAutheticate

' 检查请求是否成功
If WHTTP.Status = 200 Then
    Dim response As String
    response = WHTTP.ResponseText
    
    ' 解析JSON提取access_token
    Dim jsonParser As Object
    Set jsonParser = CreateObject("Microsoft.JScript")
    jsonParser.ExecuteScript "var res = " & response & ";"
    
    Dim accessToken As String
    accessToken = jsonParser.GetObject("res.access_token")
    
    MsgBox "获取到的access_token：" & accessToken
Else
    MsgBox "请求失败，状态码：" & WHTTP.Status & "，响应内容：" & WHTTP.ResponseText
End If

若API要求JSON格式请求体的代码 #

Dim WHTTP As Object
Set WHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    
mainUrl = "https://googlesample.api.google/okta"
myuser = "myluckyuser"
mypass = "myluckypassword"

' 构造JSON格式的请求参数
strAutheticate = "{""username"":""" & myuser & """,""password"":""" & mypass & """}"
WHTTP.Open "POST", mainUrl, False
WHTTP.setRequestHeader "Content-Type", "application/json"
' WHTTP.setRequestHeader "apikey", "[Token]"
WHTTP.send strAutheticate

If WHTTP.Status = 200 Then
    Dim response As String
    response = WHTTP.ResponseText
    
    Dim jsonParser As Object
    Set jsonParser = CreateObject("Microsoft.JScript")
    jsonParser.ExecuteScript "var res = " & response & ";"
    
    Dim accessToken As String
    accessToken = jsonParser.GetObject("res.access_token")
    
    MsgBox "获取到的access_token：" & accessToken
Else
    MsgBox "请求失败，状态码：" & WHTTP.Status & "，响应内容：" & WHTTP.ResponseText
End If

补充说明 #

• 如果你的Excel版本不支持Microsoft.JScript，可以在VBA编辑器的「工具->引用」中勾选Microsoft Scripting Runtime和Microsoft JSON Library，然后使用JSON.Parse(response)方法解析。
• 务必保留状态码检查逻辑，避免请求失败（如401、500等）时触发解析错误。

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