none
How to get share point list item by id in excel using VBA ? RRS feed

  • Question

  • Hello,

    I have Excel Workbook connected with SharePoint list that displays the list items using web services via VBA code.

    Can you please let me know, how can I get the share point list item by id in excel using VBA ?

    Thanks and Regards,


    Dipti Chhatrapati

    Monday, February 13, 2017 1:05 PM

Answers

  • Hi,

    Below thread provides sample code and suggestion for your reference.

    http://stackoverflow.com/questions/30469859/retrieving-sharepoint-lists-using-soap

    Sub spListQuery()
    
        Dim webUrl, wsdl, action, soap, xhr As XMLHTTP60
    
        itemId = 1
        listName = "UserInfo"
        webUrl = "http://youserver.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

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, February 14, 2017 2:02 AM