CODEWITHSUNDEEP
excelvbarest
smackenzie
smackenzie

Reputation: 3022

Making POST request in VBA. Message Payload getting split across lines after 1023 characters

I am making a post request via Excel VBA. While I am building one long string, the POST is failing and I am assuming it is because the string seems to be getting a carriage return at around 1023 characters. At least that is what is happening in in the Debug.Print(). If I copy the output from the console window, and put it into something like "POSTMAN", and remove the carriage return - it works. So I know the query is being assembled correctly.

Very long query string being generated with no carriage returns.

 strQuery = "{" & Chr(34) & "query" & Chr(34) & ": " & Chr(34) & "SELECT SMRTPLAN.RequestNumber AS RequestNumber,SMRTPLAN.DESCRIPTIONS AS Descriptions, SMRTGENERALPLAN.SubmissionType,SMRTPUBLISHMETA.SubmissionTypeValue, SMRTPLAN.WRType AS WRType, SMRTDISPATCHINFO.FirstDocSubmissionID, SMRTDISPATCHINFO.FinalDocIDAndVersionNumber, pr.ProductsRegNo,CountryRegNo,SMRTPLAN.WRStatus AS WRStatus FROM [DBO].[PLAN] SMRTPLAN LEFT JOIN [DBO].[GENERALPLANNING] SMRTGENERALPLAN ON SMRTPLAN.REQUESTNUMBER = SMRTGENERALPLAN.REQUESTNUMBER LEFT JOIN [DBO].[OTHERCONSIDERATIONSKPIPRINTINGINFORMATION] SMRTOTHERPRINT ON SMRTPLAN.REQUESTNUMBER = SMRTOTHERPRINT.REQUESTNUMBER LEFT JOIN [DBO].[PLANT2] SMRTPLANT2 ON SMRTPLAN.REQUESTNUMBER = SMRTPLANT2.REQUESTNUMBER LEFT JOIN [DBO].[DISPATCHINFORMATION] SMRTDISPATCHINFO ON SMRTPLAN.REQUESTNUMBER = SMRTDISPATCHINFO.REQUESTNUMBER LEFT JOIN [DBO].[PublishingMetadata] SMRTPUBLISHMETA ON SMRTPLAN.REQUESTNUMBER = SMRTPUBLISHMETA.REQUESTNUMBER "
 strQuery = strQuery & "LEFT OUTER JOIN eCTDBase SMRTBASE ON SMRTPLAN.REQUESTNUMBER = SMRTBASE.REQUESTNUMBER "
 strQuery = strQuery & "LEFT OUTER JOIN ProductRegistration pr ON SMRTPLAN.RequestNumber = pr.RequestNumber "
 strQuery = strQuery & "LEFT OUTER JOIN RegistrationMapping RM ON pr.RegistrationID =rm.RegistrationID "
 strQuery = strQuery & "WHERE  smrtplan.RequestNumber = '" & strWR & "' GROUP BY SMRTPLAN.RequestNumber,Descriptions, "
 strQuery = strQuery & "WRType, SubmissionManager, PrintDispatchCoordinator, WRStatus,RegisterData, ProductsRegNo,CountryRegNo, SubmissionTypeValue,SubmissionType,FirstDocSubmissionID,FinalDocIDAndVersionNumber" & Chr(34) & "," & Chr(34) & "dmlType" & Chr(34) & ":" & Chr(34) & "SELECT" & Chr(34) & "}"

Output in console, split over 2 lines (hard carriage return visible when I copy it out to other programs).

enter image description here

Copying it into a REST tester, and just removing this carriage return manually in the RAW post body, it works as expected, so the query is correct. In Excel I just get "bad client ID", which I think is just from what it perceives as a malformed body being sent in the POST request.

Response in something like POSTMAN of this query.

[
    {
        "SubmissionType": "Development",
        "SubmissionTypeValue": "Development: Change Clinical",
        "FirstDocSubmissionID": "",
        "FinalDocIDAndVersionNumber": "",
        "WRType": "Publishing Required",
        "ProductsRegNo": "",
        "CountryRegNo": "",
        "RequestNumber": "WR12345",
        "Descriptions": "Testing on 9th Mar - 3rd on 13thMar",
        "WRStatus": "Work Request Created"
    }
]

So how can I force Excel to not split this string into two lines. Putting it into Excel, it seems to be when we are at around 1023 characters the split happens.

People are asking for the POST code. Here it is:

 Set objRequest = CreateObject("MSXML2.XMLHTTP")
    
    ' SIT
    strUrl = "https://mycompany.com/api/t/azure/1/reg"
    client_id = "0000000000000000000000000000"
    client_secret = "11111111111111111111111111"
    
   
    
    blnAsync = True

    With objRequest
        
        .Open "POST", strUrl, blnAsync
        
        .setRequestHeader "Client_Id", client_id
        .setRequestHeader "Client_Id", client_id
        .setRequestHeader "Client_Secret", client_secret
        .setRequestHeader "Content-Type", "application/json"
        
       
        
        .Send strQuery
        
        'spin wheels whilst waiting for response
        While objRequest.readyState <> 4
            DoEvents
        Wend
        strResponse = .responseText
       
End With

Upvotes: 0

Views: 281

Answers (1)

CDP1802
CDP1802

Reputation: 16322

Try removing one of these lines

    .setRequestHeader "Client_Id", client_id
    .setRequestHeader "Client_Id", client_id

Upvotes: 1

Related Questions