Section 1.6.Kick-Start Web Services

1.6. Kick-Start Web Services

Perhaps you've looked at the object list in Figure 1-12 and thought what it really needs are code examples for each object, and hey, why not make them easy to cut and paste? You could add another column to the list, but since samples tend to be long it makes more sense to add them as attachments.

Adding attachments to a SharePoint list is straightforward: double-click the column with the paperclip icon on the row where you want to add an attachment. SharePoint displays a dialog box that lets you add file attachments, like the one in Figure 1-13.

Figure 1-13. Attachments in SharePoint

Once you've added your code samples, you'll want to be able to get them from your workbook, but there's no Excel object or method that lets you get list attachments. To do that, you'll need to use the Lists web service.

To see how this works, follow these steps:

---

Note: If you don't have the Web Service References menu option, you need to install the Office Web Services Toolkit. See Chapter 4 for more information.

---

1. Start the Visual Basic editor. Choose Tools Web Service References.

2. 
   

3. Select the Lists service and click Add. The toolkit queries the web service and builds a proxy class that you can use to call methods that the Lists web service provides.

4. Make the following changes (shown in bold ) to the AfterXmlImport event procedure created in the previous section:

       Private Sub Workbook_AfterXmlImport(ByVal Map As XmlMap, _      ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)        Dim cel As Range, ws As Worksheet, rng As Range        ' Create Web service object.        Dim lws As New clsws_Lists, xn As IXMLDOMNodeList, rowID As Integer        Set ws = ThisWorkbook.Worksheets("Sheet1")        Set rng = ws.ListObjects("List1").ListColumns("name").Range        For Each cel In rng            If Not (cel.Comment Is Nothing) Then cel.Comment.Delete            cel.AddComment cel.offset(0, 1).Text            ' Get row ID            rowID = cel.row - rng.row            ' If row ID is between 1 and the # of items in list            If rowID > 0 And rowID < rng.Rows.Count - 1 Then                ' Get the list of attachments through SharePoint Web _                   service.                Set xn = lws.wsm_GetAttachmentCollection("Excel Objects",_                          rowID)                ' If there is an attachment                If xn.Item(0).Text <> "" Then                    ' Add a hyperlink for the attachment                    ws.Hyperlinks.Add cel.offset(0, 2), _                      xn.Item(0).Text, , _                      "Click to view sample", _                      "Code sample"                End If            End If        Next    End Sub

   
   

5. In the workbook, refresh the XML data (Data XML Refresh XML Data). The code adds hyperlinks for each attachment in the SharePoint list (Figure 1-15).

   Figure 1-15. Using the Lists web service to add hyperlinks to attachments in a SharePoint list

   
   
   

Clicking on any of the links in Figure 1-15 displays the attachment from SharePoint in the browser, as shown in Figure 1-16.

Figure 1-16. Click the link to view the code sample from SharePoint