Excel VBA API token authentication

api
#1

I’m working on developing Excel tool using VBA but am stuck on authentication. I get 'Unathorized" error as JSON response. I’m new to APIs and have been reading through Asana documentation & other online sources & need some assistance on how to authenticate using personal access token.

#2

Note: I have received a personal access token from Asana for my app.

#3

Hi @Kevin_Player,

Please post the code you’re using.

#4

That’s my question, I’m not even sure which VBA code to use for authentication.
Here’s the code I am using just as testcode for getting JSON data.

Sub GetAsanaData()

Dim hReq As Object, Json As Dictionary
Dim sht As Worksheet

Set sht = Sheet1

Dim strUrl As String
    strUrl = "https://app.asana.com/api/1.0/users/me"
Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .Send
    End With

Dim response As String
    response = hReq.ResponseText
    
    MsgBox response

End Sub

#5

Well that’s pretty cool - I didn’t realize you could conduct HTTP transactions from within VBA like that, but I see you can indeed!

All you’re missing is to add this line between your .Open and your .Send:

.setRequestHeader "Authorization", "Bearer <your Personal Access Token>"

1 Like
#7

ok, thanks! I’ll give that a try as I’d rather not have to encode the key.

#8

Use my line of code instead. You want “Bearer”, not “Basic”, and you don’t need to Base64 Encode your PAT.

EDIT:
Oh well if you’re saying your code worked, then you can certainly use that! But it does more than you need.

#9

I like your way, much easier. Here’s the working code for VBA authentication:

Sub GetAsanaData()

Dim hReq As Object, Json As Dictionary
Dim sht As Worksheet
Dim authKey As String

authKey = {my Asana token key}

Set sht = Sheet1

Dim strUrl As String
    strUrl = "https://app.asana.com/api/1.0/users/me"
Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .SetRequestHeader "Authorization", "Bearer " & authKey
        .Send
    End With

Dim response As String
    response = hReq.ResponseText
    
    MsgBox response

End Sub

2 Likes