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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
| 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 Next Next 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 GetListCollection_OnError: MsgBox ("ERROR: Retrieving list collection from Sharepoint." + vbCrLf + _ Err.Number + " - " + Err.Description) End Function |
No comments:
Post a Comment