Excel 2003 Programming: A Developer's Notebook

Share a List

Excel calls sharing a list publishing . When a list is published, SharePoint Services creates a new item in the Lists folder that teammates can use to view or modify the list’s data (Figure 2-23).

How to do it

To publish a list in Excel, select the list and then choose Data → List → Publish List. Excel displays a series of steps that publish the list on the SharePoint server and display the address for the shared list (Figure 2-24).

To publish a list from code, use the ListObject’s Publish method. The first argument of the Publish method is a three-element string array containing the address of the SharePoint server, a unique name for the list, and an optional description of the list. For example, the following code publishes a list created previously:

   Set lst = ws.ListObjects("Excel Objects")
   Dim str As String
   Dim dest(2) As Variant
   dest(0) = SPSITE
   dest(1) = "Excel Objects"
   dest(2) = "Excel objects listed by date introduced"
   str = lst.Publish(dest, True)
   MsgBox "Your list has been shared. You can view it at: " & str

Figure 2-23. A published list

The Publish method returns a string containing the address of the published list. The preceding code displays that address in a message box, but you may want to navigate to that address or include a link to it somewhere on the sheet.

To add a hyperlink to the list on the SharePoint server, add a hyperlink ...