WinHttpRequest From Excel Error

[Ken Varga]

I have a requirement to get CATS data through a VBA interface on Excel.  Has anyone successfully connected using this method?

My code is simple

Dim MyRequest As New WinHttpRequest

    MyRequest.Open "GET", "https://api.catsone.nl/v3/jobs"

    'Set credentials
    MyRequest.SetCredentials "authorization: Token ", "<My API Key>", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER

    ' Send Request.

    MyRequest.Send

I have successfully retrieved data from a Non-Authorization site. 

All Help is greatly appreciated - Ken

[Ken Varga]

BTW, I get a "Authorization Type  not recognized" message (401 Error)

[Ken Varga]

I changed the VBA code to the following.

    MyRequest.Open "GET", "https://api.catsone.nl/v3/jobs"

    MyRequest.SetRequestHeader "Content-Type", "application/json"
    MyRequest.SetRequestHeader "Accept", "application/json"
    MyRequest.SetRequestHeader "Authorization", "Token " + Base64Encode("<My API Key>")
    ' Send Request.  HR_Automation

    MyRequest.Send

Going to SetRequestHeader from SetCredentials  has now gotten me to an error of "invalid Credentials".  I believe that this is an improvement.  

*****  My research has shown that the key may need to be base 64 encoded.  I have tried it with the encoding, without the encodeing, with and without the "<>" around the API key.

***** Any suggestions on what I should try next?   I know I'm close

[Steve Gagnon]

You don't need to base64 encode or add the "<>". You might also want to use & instead of + since vb might be treating the string as a number (it's been a long time since I did any vb stuff so could be wrong here).

MyRequest.SetRequestHeader "Authorization", "Token " & apiKey

[Ken Varga]

Hi Steve - Thanks for the suggestion.  I greatly appreciate your reply.   I tried all your suggestions and sill get a 401 error with a Response of    {"message":"Invalid credentials."} and a status of Unauthorized.  Do you have any other suggestions?  Have a great weekend - Ken

[Steve Gagnon]

I just tried the following code with my api key and it is working. Try it with your key. If it works maybe it will help you figure out what is wrong.

dim apiToken

apiToken = "XXXXXXXXXXXXXXXXXXXXXXXXXX"

Set objXmlHttpMain = CreateObject("Msxml2.ServerXMLHTTP")

objXmlHttpMain.open "GET", "https://api.catsone.com/v3/site", False

objXmlHttpMain.setRequestHeader "Accept", "application/json"

objXmlHttpMain.setRequestHeader "Authorization", "Token " & apiToken

objXmlHttpMain.send

MsgBox(objXmlHttpMain.responseText)

[Steve Gagnon]

Also change catsone.com to catsone.nl if that's the domain the api key is from.

[Ken Varga]

Hi Steve, - Thanks for the info.  I tried your 2nd comment 1st.  I had catsone.nl and changed it to catsone.com.  It worked just fine.  Thank you for your help, it's greatly appreciated