Excel VBA中HTTP POST请求返回400错误「Invalid request body」求助

错误原因 #

• 你误用了**表单参数拼接（x-www-form-urlencoded格式）**构建请求体，但API明确要求接收JSON格式数据，且你已在请求头中设置Content-Type: application/json，服务器会按JSON规则解析请求体，键值对拼接的字符串会被判定为无效格式，因此返回400错误。
• 嵌套参数（MyParamParentName下的子参数）无法通过表单参数的扁平结构传递，必须用JSON的嵌套对象语法来表达层级关系。

正确实现方式 #

需要构建符合要求的JSON格式字符串作为请求体，直接传递给send方法。以下是完整可运行代码：

手动构建JSON请求体并发送 #

Sub SendValidPostRequest()
    Dim httpRequest As Object
    Dim MyApiUrl As String
    Dim jsonBody As String
    
    ' 构建符合API要求的JSON字符串
    ' 注意：字符串类型参数需用双引号包裹，数字类型无需双引号
    jsonBody = "{" & _
        Chr(34) & "MyParamName1" & Chr(34) & ": MyParamValue1," & _
        Chr(34) & "MyParamName2" & Chr(34) & ": " & Chr(34) & "MyParamValue2" & Chr(34) & "," & _
        Chr(34) & "MyParamName3" & Chr(34) & ": MyParamValue3," & _
        Chr(34) & "MyParamName4" & Chr(34) & ": " & Chr(34) & "MyParamValue4" & Chr(34) & "," & _
        Chr(34) & "MyParamParentName" & Chr(34) & ": {" & _
            Chr(34) & "MyParamChildName1" & Chr(34) & ": " & Chr(34) & "MyParamChildValue1" & Chr(34) & "," & _
            Chr(34) & "MyParamChildName2" & Chr(34) & ": " & Chr(34) & "MyParamChildValue2" & Chr(34) & _
        "}" & _
    "}"
    
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    MyApiUrl = "https://MyServer/MyFolder/MyUpdateAPI"
    
    httpRequest.Open "POST", MyApiUrl, False
    httpRequest.setRequestHeader "Content-Type", "application/json"
    httpRequest.setRequestHeader "my-api-key", "my api key value"
    httpRequest.send jsonBody
    
    ' 可选：处理响应结果
    If httpRequest.Status = 200 Then
        MsgBox "请求成功：" & httpRequest.responseText
    Else
        MsgBox "请求失败，状态码：" & httpRequest.Status & vbCrLf & "错误信息：" & httpRequest.responseText
    End If
    
    Set httpRequest = Nothing
End Sub

优化建议（避免手动拼接JSON出错） #

如果参数来自Excel单元格或变量，手动拼接JSON容易出现语法错误，建议使用VBA-JSON库（需导入到VBA项目中）来构建JSON对象，示例如下：

' 需先导入VBA-JSON库到VBA项目
Sub SendPostRequestWithJsonLib()
    Dim httpRequest As Object
    Dim MyApiUrl As String
    Dim jsonObj As Object
    Dim childObj As Object
    
    ' 构建顶层JSON对象
    Set jsonObj = CreateObject("Scripting.Dictionary")
    jsonObj("MyParamName1") = MyParamValue1 ' 数字类型参数
    jsonObj("MyParamName2") = "MyParamValue2" ' 字符串类型参数
    jsonObj("MyParamName3") = MyParamValue3
    jsonObj("MyParamName4") = "MyParamValue4"
    
    ' 构建嵌套子对象
    Set childObj = CreateObject("Scripting.Dictionary")
    childObj("MyParamChildName1") = "MyParamChildValue1"
    childObj("MyParamChildName2") = "MyParamChildValue2"
    jsonObj("MyParamParentName") = childObj
    
    ' 转换为JSON字符串
    Dim jsonConverter As Object
    Set jsonConverter = CreateObject("VBA-JSON")
    Dim jsonBody As String
    jsonBody = jsonConverter.ConvertToJson(jsonObj)
    
    ' 发送请求（与手动拼接版本一致）
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    MyApiUrl = "https://MyServer/MyFolder/MyUpdateAPI"
    
    httpRequest.Open "POST", MyApiUrl, False
    httpRequest.setRequestHeader "Content-Type", "application/json"
    httpRequest.setRequestHeader "my-api-key", "my api key value"
    httpRequest.send jsonBody
    
    ' 处理响应...
    Set httpRequest = Nothing
    Set jsonObj = Nothing
    Set childObj = Nothing
    Set jsonConverter = Nothing
End Sub

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