Wednesday, 21 April 2010

VBA code to iterate through the results of GetListCollection web service from Sharepoint 2007

In a previous post I discussed how to set meta-data in Sharepoint 2007 directly from VBA.  I mentioned that the Microsoft documentation for the Sharepoint web services does not appear to be correct, insofar as you cannot address a Sharepoint "list" in the web service using its name, but rather you are forced to use the GUID.

I have now had to write a function to retrieve the GUID for the list from teh Sharepoint site.  This proved to be much harder in practice than I had expected it to be, partly at least because of the problems in working in the VBA IDE with its poor debugging functionality, partly because VBA is a much more limited language than .Net and I have got used to using all the nice constructs such as generics that make life so much easier,  and partly because I still find MSXML very "brain" hard to work with.  I don't know why I find it so hard, it just always seems that everything ends up being far more complicated than I think it should be.  I find the .Net XML model much easier to deal with.

Anyway, after playing about with XSLT and XPATH in order to extract the ID and VersionNumber attributes from the wsm_GetListCollection web service (and failing miserable), I finally decided to just iterate through the nodes and attributes till I found what I wanted.  The code below is a simple function that you can call with three parameters:  the name of the list that you need the GUID for (sListName),  a string variable in which the GUID will be placed (sListID), and an integer that the list version number will be placed (iListVersion).

Note: You may also be interested in this post: VBA code to check in a document to Sharepoint and set meta data.

Function GetListCollection(ByVal sListName As String, ByRef sListID As String, ByRef iListVersion As Integer)
    ' Class created by toolkit to connect to the Web service
    Dim ws As New clsws_Lists
    ' The collection is returned as an XML node list
    Dim ListCollectionNodeList As MSXML2.IXMLDOMNodeList
    'Root node of the returned list
    Dim nod As MSXML2.IXMLDOMNode
    ' Output string for the XML transformation
    Dim strOutput As String
    On Error GoTo GetListCollection_OnError
    ' Retrieve the collection of lists
    Set ListCollectionNodeList = ws.wsm_GetListCollection
    ' Get the root node from the list
    Set nod = ListCollectionNodeList.Item(0)
    ' Iterate nodelist to find details for our named list
    Dim bGotNode
    bGotNode = False
    Dim procNode As MSXML2.IXMLDOMNode
    Dim TitleNodeList As MSXML2.IXMLDOMNodeList
    Dim IDNodeList As MSXML2.IXMLDOMNodeList
    Set TitleNodeList = nod.SelectSingleNode("//Title")
    Set IDNodeList = nod.SelectSingleNode("//ID")
    Dim ListNode As MSXML2.IXMLDOMNode
    Dim ListChildNodes As MSXML2.IXMLDOMNode
    Dim oAttr As MSXML2.IXMLDOMAttribute
    Dim retID As String
    Dim listVersion As Integer
    Dim ListName As String
    ' Iterate through list nodes returned (should only be one)
    For Each ListNode In ListCollectionNodeList
        'Iterate through the child nodes (each one represents a single Sharepoint list)
        For Each ListChildNodes In ListNode.ChildNodes
            'See if we have any attributes (we should have!)
            If ListChildNodes.Attributes.Length > 0 Then
                ' For each list, reset our variables
                retID = ""
                listVersion = 0
                ' Iterate through the attributes
                For Each oAttr In ListChildNodes.Attributes
                    Debug.Print "Name= " & oAttr.Name
                    Debug.Print "Value= " & oAttr.nodeTypedValue
                    'Look for the ID attribute
                    If oAttr.Name = "ID" Then
                        ' FOund it, so remember it in case this is our list
                        retID = oAttr.nodeTypedValue
                    End If
                    ' Look for the "Title" attribute
                    If oAttr.Name = "Title" Then
                        If oAttr.nodeTypedValue = sListName Then
                            ' Found our list, so remember it
                            ListName = oAttr.nodeTypedValue
                            bGotNode = True
                        End If
                    End If
                    ' Look for the "Version" attribute
                    If oAttr.Name = "Version" Then
                            ' Found our list, so remember it
                            listVersion = oAttr.nodeTypedValue
                    End If
                    ' Once we have found a title, ID and version, break
                    If retID <> "" And listVersion <> 0 And bGotNode = True Then
                       Exit For
                    End If
                Next oAttr
            End If
            ' Once we have found the data, break out of iteration
            If bGotNode = True Then
                Exit For
            End If
    If bGotNode = False Then
        Err.Raise 65000, "", "ERROR: Cannot find list named <" & sListName & "> in Sharepoint site.", "", ""
    End If
    sListName = ListName
    sListID = retID
    iListVersion = listVersion
    Exit Function
    MsgBox ("ERROR: Retrieving list collection from Sharepoint." + vbCrLf + _
           Err.Number + " - " + Err.Description)
End Function

Remember, you will have to have installed the Microsoft Web Services Toolkit before this will work, and add a web reference to your Sharepoint site to your VBA project. This has been tested with Office 2003 linking to Sharepoint 2007 and works :).


  1. I do not know what are those things: (sListName),(sListID), (iListVersion) to call the function, please help me. I ´m very interested in your post...

  2. This function is used to get reference values for your list from Sharepoint.

    sListName: the name of the list that you are interested in (you supply this)
    sListID: the GUID of the list in sListName will be put in here (it tells you this)
    iListVersion: an integer that the list version number will be placed (it tells you this)

    The sListId and iListVersion can then be used with the other list methods.

  3. any ideas on how to do this in Excel 2010?