Excel 2003 Programming: A Developer's Notebook

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. Add a reference to http://server/_vti_bin/lists.asmx?wsdl, where server is the domain name of your SharePoint server. Figure 1-14 shows adding a web reference to my local SharePoint server, wombat1.

   

   Figure 1-14. Adding a reference to a SharePoint server using the Office 2003 Web Services Toolkit

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