MS Access VBA调用Azure翻译API发送HTTP请求失败排查

问题场景 #

尝试用MS Access中的VBA代码向api.cognitive.microsofttranslator.com/translate发送HTTP请求翻译文本，但执行到.send body时触发错误：

== the parameter is not correct

对应的VBA代码如下：

Public Sub translate()
     Dim strUrl As String
     Dim params As String
     Dim strResponseHeaders As String
     Dim allResponseHeader As String
     Dim strResponse As String
     Dim body As String
     Dim phrase As String: phrase = "some text to translate"
     Dim target As String: target = "mk"
     Dim some_key as string : some_key = "somekey" 
     dim location as string: location = "west..."

     body = "[{""text"":""" & phrase & """}]"
     strUrl = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0"
     params = "&from=en&to=" & target

     Dim hReq As Object
     Set hReq = CreateObject("MSXML2.XMLHTTP")
     With hReq
        .Open "POST", strUrl & params, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Content-Type", "charset=UTF-8"
        .setRequestHeader "Ocp-Apim-Subscription-Key", some_key
        .setRequestHeader "Ocp-Apim-Subscription-Region", location
        .setRequestHeader "Content-Length", Len(body)
        
        .send body
        strResponseHeaders = .StatusText
        strResponse = .responseText
        allResponseHeader = .getAllResponseHeaders

       'translate = hReq.ResponseText
    End With
End Sub

对比之下，用curl写的批处理请求能正常运行：

set "traceId=%random%%random%%random%"
set "endpoint=https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=en&to=mk"
set "requestData=[{""text"":""the price is""}]"

curl --location "%endpoint%" ^
    --header "Ocp-Apim-Subscription-Key: %key%" ^
    --header "Ocp-Apim-Subscription-Region: %region%" ^
    --header "X-ClientTraceId: %traceId%" ^
    --header "Content-Type: application/json" ^
    --header "Content-Type: charset=UTF-8" ^
    --data "%requestData%"

问题排查与解决 #

• 重复设置Content-Type导致冲突：VBA中连续两次设置Content-Type请求头，XMLHTTP对象处理重复头的逻辑和curl不同，会导致请求格式异常。需合并为单个请求头，同时指定编码：

  .setRequestHeader "Content-Type", "application/json; charset=UTF-8"
  

• 手动设置Content-Length的风险：XMLHTTP对象会自动计算并设置Content-Length，手动用Len(body)得到的是字符数而非字节数，若文本含非ASCII字符会导致长度不匹配，直接删除该行即可。
• 可选：添加X-ClientTraceId头：和curl请求对齐，便于API端排查问题，可生成随机字符串作为TraceId。

修正后的完整VBA代码 #

Public Sub translate()
     Dim strUrl As String
     Dim params As String
     Dim strResponseHeaders As String
     Dim allResponseHeader As String
     Dim strResponse As String
     Dim body As String
     Dim phrase As String: phrase = "some text to translate"
     Dim target As String: target = "mk"
     Dim some_key As String: some_key = "somekey" 
     Dim location As String: location = "west..."
     Dim traceId As String

     ' 生成随机TraceId
     traceId = CStr(Rnd() * 1000000) & CStr(Rnd() * 1000000) & CStr(Rnd() * 1000000)
     ' 构造请求体
     body = "[{""text"":""" & phrase & """}]"
     ' 构造请求URL
     strUrl = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0"
     params = "&from=en&to=" & target

     Dim hReq As Object
     Set hReq = CreateObject("MSXML2.XMLHTTP")
     With hReq
        .Open "POST", strUrl & params, False
        ' 合并Content-Type和编码
        .setRequestHeader "Content-Type", "application/json; charset=UTF-8"
        .setRequestHeader "Ocp-Apim-Subscription-Key", some_key
        .setRequestHeader "Ocp-Apim-Subscription-Region", location
        ' 添加TraceId（可选）
        .setRequestHeader "X-ClientTraceId", traceId
        
        .send body
        strResponseHeaders = .StatusText
        strResponse = .responseText
        allResponseHeader = .getAllResponseHeaders

       'translate = hReq.ResponseText
    End With
End Sub

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