CURL Equivalent to POST JSON data using VBA

Question

I know this is similar to some previously asked questions, but something is still not working for me. How can the following command:

curl -X POST --data @statements.json -H "Content-Type: application/json" --user username:password -H "x-experience-api-version: 1.0.0" https://MYLRS.waxlrs.com/TCAPI/statements

be replicated in VBA?

Extra Information:

This relates to a Hosted TIN CAN (xAPI) Learning Record Store called WaxLRS (by SaltBox). The above example comes from here: http://support.saltbox.com/support/solutions/articles/1000083945-quick

I have an account (free tinkerers account, no CC required to setup) and have generated what I believe to be the required username & password combination. The credentials are termed 'Identifier' & 'Password' and appear under a heading: Basic Authentication Credentials.

No matter what I do I get an error message:

<html>
  <head><title>Unauthorized</title></head>
  <body>
    <h1>Unauthorized</h1>
    <p>This server could not verify that you are authorized to
access the document you requested.  Either you supplied the
wrong credentials (e.g., bad password), or your browser
does not understand how to supply the credentials required.

<br/>
<!--  --></p>
    <hr noshade>
    <div align="right">WSGI Server</div>
  </body>
</html>

I believe that the example is expecting the JSON payload to be obtained from a file, but I am loading it into a string. I don't expect this to be contributing to the problem, I have compared my string with the example provided using NP++ Compare and it matches.

My code so far is:

url = "https://xxxxxxx.waxlrs.com/TCAPI/statements"
Set pXmlHttp = CreateObject("WinHttp.WinHttpRequest.5.1") 'MSXML2.XMLHTTP")
pXmlHttp.Open "POST", url, False
pXmlHttp.setRequestHeader "Content-Type", "application/json"
'pXmlHttp.setRequestHeader "Authorization", "Basic xxxxxxt8wfB6JYerYCz:xxxxxx1FOd29J1s6G2"
pXmlHttp.SetCredentials "xxxxxxt8wfB6JYerYCz", "xxxxxx1FOd29J1s6G2", 0
pXmlHttp.setRequestHeader "x-experience-api-version", "1.0.0"
pXmlHttp.send (stringJSON)
Set pHtmlObj = CreateObject("htmlfile")
pHtmlObj.body.innerHTML = pXmlHttp.responseText
apiWaxLRS = pXmlHttp.responseText

Questions/Answers that helped:

But, I'm still at a loss as to how to replicate the CURL statement in VBA

Check basic authorization in [this answer](http://stackoverflow.com/a/39862888/2165759). — omegastripes, May 20 '17 at 17:26
http://stackoverflow.com/questions/17063550/equivalent-curl-in-vba?noredirect=1&lq=1 — S Meaden, May 20 '17 at 20:31
`SetCredentials` expects the plain text User and PW values, it will handle Base64 encoding. — Bob77, May 20 '17 at 21:42
Thanks everyone... I'll have a look at the suggestions and post back :) — SlowLearner, May 20 '17 at 23:04
@omegastripes - thanks, that got me past the validation error (up'ed your answer there). Now I have a `400 Bad Request` error. I'll look into that some more... — SlowLearner, May 20 '17 at 23:30
@SMeaden - Thanks, your suggestion was actually in the same thread as my 2nd bullet point :) — SlowLearner, May 20 '17 at 23:31
@omegastripes - fixed the `400` typo on my part. Happy for you to post an answer... — SlowLearner, May 20 '17 at 23:42

score 1 · Accepted Answer · answered May 20 '17 at 23:55

Try to make basic authorization as shown in the below example:

Sub Test()

    sUrl = "https://xxxxxxx.waxlrs.com/TCAPI/statements"
    sUsername = "*******************"
    sPassword = "******************"
    sAuth = TextBase64Encode(sUsername & ":" & sPassword, "us-ascii")
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", sUrl, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Basic " & sAuth
        .setRequestHeader "x-experience-api-version", "1.0.0"
        .send (stringJSON)
        apiWaxLRS = .responseText
    End With

End Sub

Function TextBase64Encode(sText, sCharset)

    Dim aBinary

    With CreateObject("ADODB.Stream")
        .Type = 2 ' adTypeText
        .Open
        .Charset = sCharset
        .WriteText sText
        .Position = 0
        .Type = 1 ' adTypeBinary
        aBinary = .Read
        .Close
    End With
    With CreateObject("Microsoft.XMLDOM").CreateElement("objNode")
        .DataType = "bin.base64"
        .NodeTypedValue = aBinary
        TextBase64Encode = Replace(Replace(.Text, vbCr, ""), vbLf, "")
    End With

End Function

Thank you! Worked a treat, saved me my sanity (what's left of it). Cheers, — SlowLearner, May 21 '17 at 00:00

CURL Equivalent to POST JSON data using VBA

1 Answers1

Linked