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")
With objXMLHTTP
.Open "GET", url, False
.setRequestHeader "Authorization", "BPOSIDCRL <LONG BASE64 STRING>"
.setRequestHeader "X-IDCRL_ACCEPTED", "t"
.send
End With
/) to the end of the url corrected the issue for me -->url = ".../_vti_bin/idcrl.svc/"<-- try adding slash to end...