I am trying to connect to Sharepoint Online with Excel VBA script to create or delete items in a list. The problem is how to authenticate properly to SPO. I have checked the site with instruction:

When trying to follow the sequence of the instruction via a Chrome app (ARC) to test the webservices it works properly however when I build the same to complete it fails on the last step:

I am able to get a BinarySecurityToken After that there should be authed via https://XXXXX.sharepoint.com/_vti_bin/idcrl.svc to gain the SPOIDCRL cookie which can be used in every next webservice (fe REST call to create list items). however in vba I get an 401 Unauthorized error. But when I do the same in ARC it works properly. I even can use the returned Cookie in my VBA Excel to create/delete list items (after gaining context)

Dim objXMLHTTP As Object
Dim url As String

url = "https://XXXXXXX.sharepoint.com/_vti_bin/idcrl.svc"

Set objXMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

    .Open "GET", url, False
    .setRequestHeader "Authorization", "BPOSIDCRL <LONG BASE64 STRING>"
    .setRequestHeader "X-IDCRL_ACCEPTED", "t"
End With
  • had same problem, simply adding a slash (/) to the end of the url corrected the issue for me --> url = ".../_vti_bin/idcrl.svc/" <-- try adding slash to end...
    – WhiteHat
    May 16, 2018 at 18:06
  • Can you please explain what this code is doing? My requirement is - I need to make a connect from MS Access to SharePoint Online site. Access utility has Tables which are hosted at SharePoint site. So how I can make a connect to the site thorugh VBA code? Also, in the above code is there any need to pass the credentials ?
    – Rahul
    Jul 10, 2019 at 9:00


Your Answer

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

Browse other questions tagged or ask your own question.