Replace API key with Personal Token

[dsouzaerrol89]

I am trying to replace some code in Excel VBA that used API Key with a Personal Access Token. But when i just replace the API key with the new Token ID. It does not work, as in it fails in connecting to the airtable.

Set objhttp = CreateObject("MSXML2.XMLHTTP")
url = "https://api.airtable.com/v0/BASE ID/FSRates?api_key=API_KEY"
objhttp.Open "GET", url, False
objhttp.SetRequestHeader "Content-Type", "application/json"
objhttp.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objhttp.Send ("")
UCase (objhttp.ResponseText)
Dim json As Object
Dim usermatch As Boolean
usermatch = False
Set json = JSONTools.ParseJson(objhttp.ResponseText) ' root array
jsncnt = json("records").count
Dim rng, cell As Range


For i = 1 To jsncnt

Sheet57.Range("a" & i + 1).value = json("records")(i)("fields")("Title")
Sheet57.Range("b" & i + 1).value = json("records")(i)("fields")("Base")
Sheet57.Range("c" & i + 1).value = json("records")(i)("fields")("SellRate")
Sheet57.Range("d" & i + 1).value = json("records")(i)("fields")("Office")
Sheet57.Range("e" & i + 1).value = json("records")(i)("fields")("Ratesheet")
Sheet57.Range("f" & i + 1).value = json("records")(i)("fields")("FY")
Sheet57.Range("g" & i + 1).value = json("records")(i)("fields")("Index")

Next i

Set objhttp = Nothing

[Stephen_Orr1]

Hi @dsouzaerrol89,

Trying adding the following line after objhttp.SetRequestHeader "Content-Type", "application/json" (replacing YOUR_TOKEN with your PAT)

objhttp.SetRequestHeader "Authorization", "Bearer YOUR_TOKEN"

and remove ?api_key=API_KEY from the url line

Also, make sure you have enabled the data.records:read scope for your PAT in https://airtable.com/create/tokens

-Stephen

[dsouzaerrol89]

Thanks! this worked after i tested it out. Thank you!

[Stephen_Orr1]

Np - Glad to hear! Please feel free to mark my previous reply as the solution. Appreciate it!

[GavinA]

Here is a guide for anybody else struggling with this:

https://www.csvgetter.com/blog/airtable-api-key-deprecation