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 '18 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 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.