Access (and authenticate at) Zendesk web API with Excel VBA

Question

I'm trying to use a web API with Excel VBA.

In the API instructions it is written:

Using cURL

curl https://{subdomain}.zendesk.com/api/v2/users/create_or_update.json \
  -d '{"user": {"name": "Roger Wilco", "email": "roge@example.org"}}' \
  -H "Content-Type: application/json" -X POST \
  -v -u {email_address}:{password}

Link to the API itself (Create or Update User) https://developer.zendesk.com/rest_api/docs/support/users#create-or-update-user

This is my code:

Public Function PostJsonRequest() As String
    Dim strURL As String
    Dim strParse() As String
    Dim jsonStr As String
    Dim hreq As Object
    Dim tixScript As Object
    
    On Error GoTo Er   
 
    Set hreq = CreateObject("MSXML2.XMLHTTP")        
    strURL = "https://subdomain.zendesk.com/api/v2/users/create_or_update"
    hreq.Open "POST", strURL, 0, "username/token", "token"
    
    hreq.setRequestHeader "User-Agent", "Chrome"
    hreq.setRequestHeader "Content-Type", "application/json"
    hreq.setRequestHeader "Accept", "application/json"
    hreq.setRequestHeader "-v -u {MyEmail}:{MyPassword}"

    jsonStr = "-d '{""user"": {""name"": ""Roger Wilco"", ""email"": ""roge@example.org""}}'"
    hreq.Send jsonStr

    MsgBox hreq.responseText
    Exit Function
    
    Er:
    MsgBox "Error - " & Err.Number & " - " & Err.Description    
End Function

In the Email and Password line I get this error:

Error - 450 - Wrong number of arguments or invalid property assignment

Pᴇʜ · Accepted Answer · 2021-03-16T09:52:57.403

This is not valid hreq.setRequestHeader "-v -u {MyEmail}:{MyPassword}"

Try basic authentication instead

hreq.setRequestHeader "Authorization", "Basic dXNlcjpwYXNzd29yZA=="

where dXNlcjpwYXNzd29yZA== is the base64 encoded {MyEmail}:{MyPassword} string.

For example:

Dim username As String
username = "user123"

Dim password As String
password = "abc123"

hreq.setRequestHeader "Authorization", "Basic " & EncodeBase64(username & ":" & password)

Where the base64 encoding function works like this:

Private Function EncodeBase64(ByVal plainText As String) As String
    Dim bytes() As Byte
    Dim objXML As Object 'MSXML2.DOMDocument60
    Dim objNode As Object 'MSXML2.IXMLDOMNode
    
    bytes = StrConv(plainText, vbFromUnicode)
   
    Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = bytes
    EncodeBase64 = objNode.Text
    
    Set objNode = Nothing
    Set objXML = Nothing
End Function

Also make sure you only send the JSON part without the -d '…':

jsonStr = "{""user"": {""name"": ""Roger Wilco"", ""email"": ""roge@example.org""}}"

Finally a more cosmetic thing than an issue:

hreq.setRequestHeader "User-Agent", "Chrome"

Either set your user agent string to fake a real user agent, for a current chrome it would look like:

Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36

Faking a user agent is to make the website think you are surfing with a Chrome for example. For the API this is not necessary I guess, so you can set it to something generic like:

hreq.setRequestHeader "User-Agent", "MyVBAProject Version x.y.z Windows 10 using MSXML2.XMLHTTP"

to show the website clearly which type of application you are.

At least don't set it to "Chrome" as this is just confusing as Chrome would never use that user agent.

Access (and authenticate at) Zendesk web API with Excel VBA

1 Answers1