Section 2.14.Use the Lists Web Service | Excel 2003 Programming: A Developers Notebook (Developers Notebook)

2.14. Use the Lists Web Service

SharePoint Services includes the Lists web service for getting at shared lists and their data directly. The Lists web service lets you perform tasks on the server that you cannot otherwise perform through Excel objects. Specifically, you can use the Lists web service to:

Add an attachment to a row in a list
Retrieve an attachment from a row in a list
Delete an attachment
Delete a list from a SharePoint server
Look up a list GUID
Perform queries

Note: I'm jumping ahead a bit hereweb services are covered in Chapter 4. You need to know a bit about the Lists web service now because you can't delete a list from SharePoint or add attachments without it.

2.14.1. How to do it

To use a web service from Visual Basic:

Install the Web Services Toolkit from Microsoft at www.microsoft.com/downloads .
Close and restart Excel.
Open the Visual Basic Editor and select Web References from the Tools menu. Visual Basic displays the Microsoft Office 2003 Web Services Toolkit references dialog.

The SharePoint web services reside in the _vti_bin folder of the SharePoint site. To use the Lists web service:

Display the Microsoft Office 2003 Web Services Toolkit as described above.
Select the Web Service URL option button and type the address of the address using this form: http://sharepointURL/_vti_bin/lists.asmx .
Click Search. The Toolkit should find the Lists web service and display it in the Search Results list.
Select the checkbox beside Lists in Search Results and click Add.
The Toolkit generates a proxy class named clsws_Lists and adds it to the current project.

The SharePoint server must authenticate the Excel user before you can call any of the Lists web service methods. If the user has not been authenticated, a "Maximum retry on connection exceeded" error occurs. In Visual Basic .NET or C# .NET, you authenticate the user from code by creating a Credentials object for the user. For example, the following .NET code passes the user's default credentials to a web service:

    wsAdapter.Credentials = System.Net.CredentialCache.DefaultCredentials

Unfortunately, you can't do that directly in Excel's Visual Basic. Instead, you must use one of the following techniques to connect to the SharePoint server through Excel:

Update or refresh a worksheet list that is shared on the server
Insert an existing SharePoint list on a worksheet; this can even be a dummy list placed on the server solely for the purpose of establishing connections
Navigate to the SharePoint server in code, as shown in the section "Sharing Lists"

Any of these techniques displays the SharePoint authentication dialog box and establishes a user session for Excel. Afterward, you can call Lists methods and they will be authorized using the current session.

: Debugging tip

One thing you will notice fairly quickly when using the Lists Web service is that the error reporting is minimal. When a method fails on the server side, you receive only a general error. To receive more detail, make the following modification to the clsws_Lists ListsErrorHander procedure:

    Private Sub ListsErrorHandler(str_Function As String)        If sc_Lists.FaultCode <> "" Then           Err.Raise vbObjectError, str_Function, sc_Lists.FaultString & _              vbCrLf & sc_Lists.Detail        'Non SOAP Error        Else            Err.Raise Err.Number, str_Function, Err.Description        End If    End Sub

Now errors will be reported with details from the server.

2.14.2. How to add attachments to a list

Excel does not directly support attachments to lists. However, you can use the Lists web service AddAttachment method to add a file attachment to a row in a list, then use GetAttachmentCollection to retrieve attachments from within Excel.

For example, the following code attaches the image file joey.jpg to the second row of a shared list:

    ' Requires web reference to SharePoint Lists.asmx    Dim lws As New clsws_Lists, str As String    src = ThisWorkbook.Path & "\joey.jpg"    dest = lws.wsm_AddAttachment("Excel Objects", "2", "joey.jpg",  FileToByte(src))

The AddAttachment method's last argument is an array of bytes containing the data to attach. To convert the image file to an array of bytes, the preceding code uses the following helper function:

    Function FileToByte(fname As String) As Byte(  )        Dim fnum As Integer        fnum = FreeFile        On Error GoTo FileErr        Open fname For Binary Access Read As fnum        On Error GoTo 0        Dim byt(  ) As Byte        ReDim byt(LOF(fnum) - 1)        byt = InputB(LOF(fnum), 1)        Close fnum        FileToByte = byt        Exit Function    FileErr:        MsgBox "File error: " & Err.Description    End Function

2.14.3. How to retrieve attachments

Use the Lists web service GetAttachmentCollection method to retrieve an attachment from a list. The GetAttachmentCollection method returns an XML node list that contains information about each attachment for the row. The following code retrieves the location of the file attached in the previous section:

    Dim lws As New clsws_Lists  ' Requires web reference to     SharePoint Lists.asmx    Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML    Set xn = lws.wsm_GetAttachmentCollection("Excel Objects", "2")    ThisWorkbook.FollowHyperlink (xn.Item(0).Text)

Notice that the returned XML node list is a collection since rows can have multiple attachments. Since the preceding example only attached one file, this sample simply retrieves the first item from the node list. The Text property of this item is the address of the attachment on the SharePoint server.

2.14.4. How to delete attachments

Finally, it is very simple to delete an attachment using the DeleteAttachment method:

    Dim lws As New clsws_Lists  ' Requires Web reference to to SharePoint  Lists.asmx    lws.wsm_DeleteAttachment "Excel Objects", "2", _    SPSITE & "/Lists/Excel Objects/Attachments/2/joey.jpg"

Since DeleteAttachment requires the fully qualified address of the attachment, it is useful to save the address of each attachment somewhere on the worksheet or to create a helper function to retrieve the address from the SharePoint server:

    Function GetAttachment(ListName As String, ID As String) As String        Dim lws As New clsws_Lists ' Requires Web reference to to SharePoint  Lists.asmx        Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML        Set xn = lws.wsm_GetAttachmentCollection(ListName, ID)        GetAttachment = xn.Item(0).Text    End Function

2.14.5. How to 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 applicationsor especially ambitious programmersmay 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:

    Dim lws As New clsws_Lists  ' Requires Web reference to 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("Excel Objects", "", query, _      viewFields, rowLimit, queryOptions)

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

    Debug.Print xn.Item(0).xml

The key to the preceding query is the XML supplied to the LoadXml method. You create conditional queries using the Query element and determine the columns included in the results using the ViewFields element. Perhaps the simplest way to create these queries is to write them as a text file in an XML editor (or Notepad), then load them from that file using the Load method:

    xdoc.Load ("query.xml")

The query file takes this form:

     <Document>    <Query>       <OrderBy>           <FieldRef Name="Column 1" Asending="FALSE"/>        </OrderBy>       <Where>          <Gt>             <FieldRef Name="_x0031_" />             <Value Type="Value">6</Value>          </Gt>       </Where>    </Query>    <ViewFields>           <FieldRef Name="ID" />           <FieldRef Name="_x0031_" />           <FieldRef Name="_x0032_" />           <FieldRef Name="_x0033_" />    </ViewFields>    <QueryOptions>       <DateInUtc>FALSE</DateInUtc>       <Folder />       <Paging />       <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>       <MeetingInstanceId />       <ViewAttributes Scope="Recursive" />       <RecurrenceOrderBy />       <RowLimit />       <ViewAttributes />       <ViewXml />    </QueryOptions>    </Document>

Notice that the FieldRef elements sometimes use the internal SharePoint names to identify columnslists don't always use the titles displayed in the columns as column names. You can get the internal column names by examining the list's XML. To see the list's XML, use the GetList method, as shown here:

    Dim lws As New clsws_Lists    Dim xn As IXMLDOMNodeList ' Requires reference to Microsoft XML    Set xn = lws.wsm_GetList ("Excel Objects")    Debug.Print xn(0).xml

2.14.6. What about...

To learn about	Look here
Installing the Web Services Toolkit	Navigate to www.microsoft.com/downloads and search for "Web Services Toolkit"
Lists web service	msdn.microsoft.com/library/en-us/spptsdk/html/soapcLists.asp
DOMDocument	msdn.microsoft.com/library/en-us/xmlsdk/htm/xml_obj_overview_20ab.asp
IXMLDOMNodeList	msdn.microsoft.com/library/en-us/xmlsdk30/htm/xmobjxmldomnodelist.asp
Query element	msdn.microsoft.com/library/en-us/spptsdk/html/tscamlquery.asp
ViewFields element	msdn.microsoft.com/library/en-us/spptsdk/html/ tscamlviewfields.asp
QueryOptions element	msdn.microsoft.com/library/en-us/spptsdk/html/tsc-SPQuery.asp
Batch element	msdn.microsoft.com/library/default.asp?url=/library/en-us/spsdk11/caml_schema/spxmlelbatch.asp