Excel VBA API token authentication

English Forum Developers & 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
10

I’ve been trying to implement this but it doesn’t seem to work for me - have you any other examples you can share?

11

Hi @Stuart_Radlett,

You generated a Personal Access Token that you’re using as your token key?

If so, please post the exact error message you’re getting and where you’re getting it.

12

Hello Phil, after many months I have finally managed to return to this and sucess! Could you advise how I would get a specific value of tag from response? For example Name?

Many thanks

13

Hi @Stuart_Radlett,

I’m not entirely sure as I haven’t worked in this environment. Can you post what you’re getting back in the “response” variable?

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.