Generate Token with VBA in MS Excel

[DylanJeffries]

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=O...>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

[RandyBurton]

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

[StuartMoore]

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

[DylanJeffries]

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 

[ReubenWolff]

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