I'm trying to use the rest api to get info about a doc library but keep getting 401 unauthorized - the code below worked for a bit for me but not other users now it doesn't work for me either...

I modified this answer to generate an SPOIDCRL token of the form "SPOIDCRL=[big long string]"

Now I'm querying the sharepoint doc library and passing the SPOIDCRL in the header:

Function GetSPList(Optional boolGetAuthCookie As Boolean = True) As String
'Dim xmlHttp As MSXML2.XMLHTTP60
'Set xmlHttp = New MSXML2.XMLHTTP60
Dim xmlHttp As WinHttp.WinHttpRequest
Set xmlHttp = New WinHttp.WinHttpRequest
''Dim xmlHttp As MSXML2.ServerXMLHTTP60
''Set xmlHttp = New MSXML2.ServerXMLHTTP60

I've tried all three of the above for the query, same result

Dim strListsAPI As String

strListsAPI = "_api/web/lists('" & [{guid of doc library}] & "')/"
Dim strURL As String, strHeader As String, strBody As String
Dim strDummy As String, strToken As String

If boolGetAuthCookie Then
   strToken = testAuth ' this is the function adapted from the linked answer
End If
'Debug.Print strToken
strURL = [https://domain/site/] & strListsAPI & "Items" '& "?$select=FieldValuesAsText"

strURL = strURL + "?$select=FileLeafRef,Modified"   
strURL = strURL & "&$filter=FileLeafRef eq '[Filename].xlam'" '& randomURLAppend

Debug.Print strURL

If i copy the url created above and paste into a browser I get a result returned in the browser that I expect. ("randomURLAppend" was a thing that I tried where I added a random parameter to the end of the url in case it was something to do with the caching of the results - it didn't work)

With xmlHttp
.Open "GET", strURL, False
     .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
     .setRequestHeader "Cache-Control", "no-cache"

     .setRequestHeader "Authorization", "Bearer " & strToken
     .setRequestHeader "X-FORMS_BASED_AUTH_ACCEPTED", "f"
    .setRequestHeader "accept", "application/xml"
'    Debug.Print .readyState
     .send ("")
GetSPList = .responseText
    Debug.Print "###############", strDummy, .Status, .StatusText

End With Set xmlHttp = Nothing End Function

I think that it might be something to do with the authentication cookie (SPOIDCRL) not being recognised or maybe expiring, however this is my first proper attempt at doing HTTP requests (I'm not a professional, I'm just trying to solve a problem at work...)

I've double and triple checked the permission on the library and I have full read/write access.

Can anyone help... please?

  • Adding this answer in case anyone else comes across the same issue. My function that retrieved the cookie does so with an WinHTTP request. I was then creating a new WinHTTP request in the code above. I solve this by passing a winHTTPRequest object (byref) to the function that retrieves the SPOIDCRL cookie and then using that same WinHTTP object to make my final request to the REST api. So, I passed the xmlHTTP object to the authorisation function, then used it to make my final request. I've no idea how why it works but it does.
    – Ben0506
    Jul 29, 2019 at 20:21

1 Answer 1


it is because you need to create a client ID for you application

**enter here to create your ID


**Enter Here to authorize your APP


**do not Forget set this xml you can figure out where!! :)

<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection/web" Right="FullControl"/>

**FINALLY in your app, ask for authorization each time you make a post... THE REALM value can be taken from https://yourtenantname.sharepoint.com/_layouts/15/appprincipals.aspx

Set objAut = CreateObject("MSXML2.XMLHTTP.6.0")
objAut.Open "POST", "https://accounts.accesscontrol.windows.net/{realm}/tokens/OAuth/2", False

this post should send in the BODY a string like this :

Body="appReg_clientId=undefined&appReg_clientSecret=undefined&targetHost=undefined&principal=undefined&realm=undefined&grant_type=client_credentials&client_id={the clienID}@{the realm value}&client_secret={the secretID for you app}&resource=00000003-0000-0ff1-ce00-000000000000/yourtenantname.sharepoint.com@{the realm}"

and in the headers use this

objAut.setRequestHeader "Access-Control-Allow-Headers", "*"
objAut.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objAut.setRequestHeader "Access-Control-Allow-Origin", "*"

this should return a JSON string with the values for the token...

use this token to make the next request to sharepoint.. and remember.. this token is available only for few minutes... thats the reason to ask a new Token every time.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.