WinHttpRequest From Excel Error

131 views
Skip to first unread message

Ken Varga

unread,
Aug 9, 2022, 5:08:42 PM8/9/22
to CATS API v3
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

unread,
Aug 9, 2022, 5:10:55 PM8/9/22
to CATS API v3
BTW, I get a "Authorization Type  not recognized" message (401 Error)

Ken Varga

unread,
Aug 10, 2022, 3:03:12 PM8/10/22
to CATS API v3
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

unread,
Aug 10, 2022, 5:23:00 PM8/10/22
to CATS API v3
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

unread,
Aug 12, 2022, 12:25:55 PM8/12/22
to CATS API v3
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

unread,
Aug 12, 2022, 1:32:53 PM8/12/22
to CATS API v3
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

unread,
Aug 12, 2022, 1:34:37 PM8/12/22
to CATS API v3
Also change catsone.com to catsone.nl if that's the domain the api key is from.

Ken Varga

unread,
Aug 14, 2022, 9:41:02 PM8/14/22
to CATS API v3
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
Reply all
Reply to author
Forward
0 new messages