0

I'm currently trying to complete a table with information from the items of a Sharepoint Online list, using VBA code executed from Microsoft Word macro. I've successfully managed to achieve this when the list was hosted on Sharepoint 2013.

This is the code I'm trying to use:

Dim SOAPURL_List As String, SOAPListName As String, SOAPViewName As String
SOAPURL_List = "https://<host>.sharepoint.com/sites/<site_name>/_vti_bin/Lists.asmx"
SOAPListName = listGuid

Dim SOAPAction As String
SOAPAction = "http://schemas.microsoft.com/sharepoint/soap/GetListItems"
Dim SOAPEnvelope_Pre As String, SOAPEnvelope_Pst As String
SOAPEnvelope_Pre = "<?xml version=""1.0"" encoding=""utf-8""?>" & _
"<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
"<soap:Body>"
SOAPEnvelope_Pst = "</soap:Body>" & _
"</soap:Envelope>"

Dim SOAPMessage As String

SOAPMessage = SOAPEnvelope_Pre & _
" <GetListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
" <listName>" & SOAPListName & "</listName>" & _
" </GetListItems>" & _
SOAPEnvelope_Pst

Dim Request As Object
Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0")

Request.Open "POST", SOAPURL_List
, False, curUsername, curPassword
Request.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
Request.setRequestHeader "SOAPAction", SOAPAction
Request.setRequestHeader "X-FORMS_BASED_AUTH_ACCEPTED", "f"

Request.Send (SOAPMessage)
Set getDataFromSharepoint = 
Request.responseXML.getElementsByTagName("z:row")

Unfortunately, I don't get any result. I've read that the authentication process is different for Sharepoint Online, but I haven't been able to find enough information about this.

In this case, could you, please, tell me what could be wrong with the above code? Or, at least, point me to where can I find information about the authentication?

Thank you!

1

You're right regarding to authentication issue. SharePoint Online uses the O365 authentication system. Available options explained here with sample.

As advice, modify your macro to use the rest API instead of the deprecated SOAP webservices and add an App Authentication process before to retreive the access token to be reused on your following queries.

  • Thank you for your help! I ended up using C# instead of VB and it has been much easier. The authentication is a really simple process and populating the table is similar to VB. But thanks anyway for your answer! – eOf Feb 15 '18 at 10:20
0

Change below lines

Dim Request As Object
Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0")

By this:

Dim Request As MSXML2.XMLHTTP30
Set Request = New MSXML2.XMLHTTP30

It will work - make sure if you do not pass a username and password, the site should open in IE.

If you do not want to open the site in IE you need to pass username and password.

Your Answer

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

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