topic Re: Generate Token with VBA in MS Excel in ArcGIS REST APIs and Services Questions

Generate Token with VBA in MS Excel

DylanJeffries — Sat, 11 Dec 2021 17:32:33 GMT

Hi all,

I have a current request to download field collected data and pics hosted in ArcGIS Online and format in an Excel table.

In the back end of Excel (VBA) have managed to connect to ArcGIS online and generate a token with the following 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=" & Me.userName_Txt.Value & "&password=" & Me.password_Txt.Value & "&client=requestip&expiration=1"
tokenHTTP.Open "POST", tokenURL, False
tokenHTTP.setRequestHeader "Content-type", "application/json"
tokenHTTP.send (tokenJSON)
Debug.Print (tokenHTTP.responseText)‍‍‍‍‍‍‍‍‍

the response looks like this.

{
 "token":"hIBzdwwVbXv9k8iIZtr0LBwZ5ujSwCdTrdS6_P1XngsglCLnB2tpiV8u9W-6oMDA9Ggf6EMMd77SmXH6BPszKSGvf_oz9QTnEyt4YsSZ-nCuUDzhxW8j6oaAKET8Hk-4qBnLiXGEpuz8JifBSLwHFg..",
 "expires":1541634562738,
 "ssl":true
}‍‍‍‍‍‍

The problem is, when I try to use this token to query a FeatureService like this

Dim httpObj As Object
Set httpObj = CreateObject("MSXML2.ServerXMLHTTP")

Dim responseJSON As String
Dim featureServiceURL As String
featureServiceURL = "https://services8.arcgis.com/myagolid/arcgis/rest/services/test_points/FeatureServer/0/query?where=OBJECTID>1&outFields=*&returnHiddenFields=true&returnGeometry=true&returnExceededLimitFeatures=true&f=pgeojson&token=" & agoToken
Debug.Print ("Get Data from: " & featureServiceURL)

httpObj.Open "POST", featureServiceURL, False
httpObj.setRequestHeader "Content-type", "application/json"
httpObj.send (responseJSON)
Debug.Print (responseJSON)‍‍‍‍‍‍‍‍‍‍‍‍

I get this error?

{
 "error" :
 {
 "code" : 498,
 "message" : "Invalid token.",
 "details" : [
 "Invalid token."
 ]
 }
}‍‍‍‍‍‍‍‍‍‍

I cant work out what is going on here? Is it not returning the full length of the token? Or do I have to configure something in my ArcGIS Online account to allow access?

Any help would be much appreciated

Thanks

James

Re: Generate Token with VBA in MS Excel

RandyBurton — Thu, 08 Nov 2018 00:47:22 GMT

Just venturing a guess...

The > in your where clause probably needs to be escaped with %3E. OBJECTID%3E1

You could omit the where as a test.

HTML URL Encoding Reference

Re: Generate Token with VBA in MS Excel

StuartMoore — Thu, 07 Mar 2019 11:56:21 GMT

did you ever get this to work, i'm having the same issues, i can get a token but it doesn't work, i even tried the generated token directly in Chrome against the feature service and it says its invalid

Stu

Re: Generate Token with VBA in MS Excel

DylanJeffries — Tue, 19 Mar 2019 06:21:40 GMT

Sorry Stuart, I ended up giving up on this approach and developed a NodeJS application that exports the feature service an images to an excel file. Excel VBA doesn't deal well with JSON either which was another reason to move to node

Re: Generate Token with VBA in MS Excel

ReubenWolff — Wed, 29 May 2024 19:46:54 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