topic Re: Excel vba request a token from Rest API appears to be 106 characters shorter than token generated by arcgis online query tools in ArcGIS REST APIs and Services Questions

Excel vba request a token from Rest API appears to be 106 characters shorter than token generated by arcgis online query tools

Larry — Tue, 20 Aug 2019 20:39:37 GMT

I am trying create a VBA tool in Excel to insert data into a AGOL Feature layer. When I request a token via VBA it ends with .. and is 106 characters shorter than the token generated by the AGOL query tool. when I try to use the token I request I get and "invalid token" error. But if I copy the AGOL generated token and paste it into my code my query works fine. It looks like the VBA requested token is cut off.

Anyone else run into this?

Thanks

Larry

A snippet of my code:

Dim tokenHTTP As Object
Set tokenHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Dim tokenJSON As String
Dim tokenURL As String
tokenURL = "https://www.arcgis.com/sharing/rest/generateToken?f=json&username=MyuserName&password=MyPassword&client=requestip&expiration=1"

tokenHTTP.Open "POST", tokenURL, False
tokenHTTP.setRequestHeader "Content-type", "application/json"
tokenHTTP.send (tokenJSON)

Debug.Print tokenHTTP.responseText

Re: Excel vba request a token from Rest API appears to be 106 characters shorter than token generated by arcgis online query tools

ReubenWolff — Wed, 29 May 2024 19:40:49 GMT

I was having the same issue until I changed the referer to the https of my feature service.

I also downloaded the module for parsing the json at: https://github.com/VBA-tools/VBA-JSON

Here is the function that works for me:

Public Function getToken()
Dim httpReq As Object
Dim response As String
Dim USERNAME, PASSWORD
Dim tkn As Variant
Dim myFeature

USERNAME = "xxxxx"
PASSWORD = "xxxxx"
myFeature = "https://services.arcgis.com/xxxxxxxx/arcgis/rest/services/xxxxx/FeatureServer"
Set httpReq = CreateObject("MSXML2.ServerXMLHTTP")

httpReq.Open "GET", "https://www.arcgis.com/sharing/generateToken?f=pjson&username=" & USERNAME & "&password=" & PASSWORD & "&client=&ip=&referer=" * myFeature & "&expiration=1"
httpReq.send
response = httpReq.responseText
Set JSON = JsonConverter.ParseJson(response)
'Debug.Print httpReq.responseText

tkn = JSON("token")

getToken = tkn

End Function

Re: Excel vba request a token from Rest API appears to be 106 characters shorter than token generated by arcgis online query tools

ReubenWolff — Thu, 30 May 2024 01:03:22 GMT

Corrected vba. I had an asterisk instead of an ampersand:

Here is the corrected function:

Public Function getToken()
Dim httpReq As Object
Dim response As String
Dim USERNAME, PASSWORD
Dim tkn As Variant
Dim myFeature

USERNAME = "xxxxx"
PASSWORD = "xxxxx"
myFeature = "https://services.arcgis.com/xxxxxxxx/arcgis/rest/services/xxxxx/FeatureServer"
Set httpReq = CreateObject("MSXML2.ServerXMLHTTP")

httpReq.Open "GET", "https://www.arcgis.com/sharing/generateToken?f=pjson&username=" & USERNAME & "&password=" & PASSWORD & "&client=&ip=&referer=" & myFeature & "&expiration=1"
httpReq.send
response = httpReq.responseText
Set JSON = JsonConverter.ParseJson(response)
'Debug.Print httpReq.responseText

tkn = JSON("token")

getToken = tkn

End Function