Retrieving sharepoint lists using SOAP

Question

I am trying to pull specific data points from a sharepoint list using VBA and SOAP. However, I am very new to both VBA and sharepoint, so there a few things that do not make sense to me. Currently, I'm trying to use this site as a reference:

http://depressedpress.com/2014/04/05/accessing-sharepoint-lists-with-visual-basic-for-applications/

I am having problems trying to locate where to find the credential information on the sharepoint site. Where would I go exactly to find the username, password, and SOAP URL?

Answer 1

I wrote the simple SharePoint list SOAP query below in VBA. I was able to run it within Excel and pull data from SharePoint. SOAP is not easy to work with as you need to have all the XML correct or it fails.

You can get a list of the services with examples by opening the /_vti_bin/Lists.asmx link on your SharePoint server, e.g., http://yourserver.com/_vti_bin/Lists.asmx And if you get a blank page then it means your version of SharePoint doesn't support SOAP web services.

Example: Queries the SharePoint UserInfo list for data using VBA

Sub spListQuery()

    Dim webUrl, wsdl, action, soap, xhr As XMLHTTP60

    itemId = 1
    listName = "UserInfo"
    webUrl = "http://yourserver.com"   'set to SharePoint site url
    wsdl = "/_vti_bin/Lists.asmx"
    action = "http://schemas.microsoft.com/sharepoint/soap/GetListItems"

soap = "<?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>" & _
        "<GetListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
        "<listName>" & listName & "</listName>" & _
        "<query><Query>" & _
            "<Where><Eq><FieldRef Name=""ID""/><Value Type=""Integer"">" & itemId & "</Value></Eq></Where>" & _
            "</Query></query>" & _
            "<viewFields><ViewFields/></viewFields>" & _
        "</GetListItems>" & _
    "</soap:Body>" & _
"</soap:Envelope>"


    Set xhr = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    xhr.Open "POST", webUrl & wsdl, False
    xhr.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    xhr.setRequestHeader "SOAPAction", action
    xhr.Send soap

    MsgBox xhr.Status & ":" & xhr.statusText & vbCrLf & xhr.responseText


End Sub