Generate Token with VBA in MS Excel

Question

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

ReubenWolff · Answer

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 = "xyz"
PASSWORD = "xyz"
myFeature = "https://services.arcgis.com/xyz/arcgis/rest/services/xyz/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

DylanJeffries · Answer

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

StuartMoore · Answer

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