400 Error "Invalid request body" on HTTP POST in Excel VBA

Question

I've been tasked with doing an HTTP POST in Excel VBA. I have an HTTP GET that's working fine in the same context.

First, here's the HTTP GET that's working fine in this context, with names generalized:

Set httpRequest = CreateObject("MSXML2.XMLHTTP")
MyApiUrl = "https://MyServer/MyFolder/MySearchAPI?MyParamName1=MyParamValue1&MyParamName2=MyParamValue2"
httpRequest.Open "GET", MyApiUrl, False
httpRequest.setRequestHeader "Content-Type", "application/json"
httpRequest.setRequestHeader "my-api-key", "my api key value"
httpRequest.send

Here's everything I've been told to get this HTTP POST working:

First, the name of the API is MyUpdateAPI. The URL path to it is the same: https://MyServer/MyFolder/MyUpdateAPI

Second, the parameters (in XLM format?) are as follows, again with names generalized:

{
    "MyParamName1": MyParamValue1,
    "MyParamName2": "MyParamValue2",
    "MyParamName3": MyParamValue3,
    "MyParamName4": "MyParamValue4",
    "MyParamParentName": {
        "MyParamChildName1": "MyParamChildValue1",
        "MyParamChildName2": "MyParamChildValue2"
    }
}

Some coworkers have a test tool that they paste that XML into and it returns 200, success.

I don't know how to form the XML parent/child params into HTTP parameters.

Using the sample HTTP POST in Excel VBA at https://codingislove.com/http-requests-excel-vba/ as a guide, here's what I've tried:

'build params:
MyParam1 = "MyParamName1=MyParamValue1" '(no ampersand on the first param).
MyParam2 = "&MyParamName2=MyParamValue2"
MyParam3 = "&MyParamName3=MyParamValue3"
MyParam4 = "&MyParamName4=MyParamValue4"
MyParamChild1 = "&MyParamChildName1=MyParamChildName1"
MyParamChild2 = "&MyParamChildName2=MyParamChildName2"
sAllParams = MyParam1 & MyParam2 & MyParam3 & MyParam4 & MyParamChild1 & MyParamChild2

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 sAllParams

Can anyone shed any light on what I'm doing wrong? How should the XML parent/child params be formatted in the HTTP parameter string?

Answer 1

    Dim data
   ' some test data
   data = Array(, 1, "A", 2, "B", "C", "D")
   
   Dim sAllParams As String
   sAllParams = "{'MyParamName1':" & data(1) & _
    ",'MyParamName2': '" & data(2) & _
    "','MyParamName3': " & data(3) & _
    ",'MyParaName4': '" & data(4) & _
    "','MyParamParentName': {" & _
    "'MyParamChildName1':'" & data(5) & _
    "','MyParamChildName2':'" & data(6) & _
    "'}}"
    sAllParams = Replace(sAllParams, "'", """")
    
    Debug.Print sAllParams

    httpRequest.send sAllParams