Programming Excel with VBA and .NET

Perform Queries

In general, you don’t need to perform queries through the Lists Web Service. Most of the operations you want to perform on the list data are handled through the Excel interface or through the Excel list objects as described previously.

However, advanced applications—or especially ambitious programmers—may use the Lists Web Service to exchange XML data directly with the SharePoint server. For instance, you may want to retrieve a limited number of rows from a very large shared list. In this case, you can perform a query directly on the SharePoint list using the GetListItems method. For example, the following code gets the first 100 rows from a shared list:

Sub QueryList(  )
    Dim lws As New clsws_Lists  ' Requires web reference to SharePoint Lists.asmx
    Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML
    Dim query As IXMLDOMNodeList
    Dim viewFields As IXMLDOMNodeList
    Dim rowLimit As String
    Dim queryOptions As IXMLDOMNodeList
    rowLimit = "100"
    Dim xdoc As New DOMDocument
    xdoc.LoadXml ("<Document><Query /><ViewFields />" & _
      "<QueryOptions /></Document>")
    Set query = xdoc.getElementsByTagName("Query")
    Set viewFields = xdoc.getElementsByTagName("Fields")
    Set queryOptions = xdoc.getElementsByTagName("QueryOptions")
    Set xn = lws.wsm_GetListItems("Test List", "", query, _
      viewFields, rowLimit, queryOptions)
End Sub

The results are returned as XML. To see them, you can simply display the root node of the returned object as shown here:

Debug.Print xn.Item(0).xml

The key to ...