Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 09, 2023 06:19 AM
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
Jun 10, 2023 07:03 AM
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
Jun 12, 2023 07:41 AM
Thanks! this worked after i tested it out. Thank you!
Jun 12, 2023 09:14 AM
Np - Glad to hear! Please feel free to mark my previous reply as the solution. Appreciate it!
Jan 04, 2024 06:08 AM
Here is a guide for anybody else struggling with this: