5.3. Sharing ListsIn Microsoft Excel 2003, lists are ranges of cells that can easily be sorted, filtered, or shared. Lists are a little different from the AutoFilter feature available in earlier versions of Excel, in that lists are treated as a single entity rather than just a range of cells . This cohesion is illustrated by a blue border that Excel draws around the cells in a list. Lists have these advantages over AutoFilter ranges:
That last item is the key advantage of listslists are really just ways to share information that fits into columns and rows. 5.3.1. Viewing SharePoint Lists in ExcelThere are many different ways to create lists in SharePoint. In fact, since SharePoint uses lists everywhere, most tasks involve either creating lists or adding new items to them. For example, the document workspace created in the preceding sections contains several lists: Announcements, Shared Documents, Members, Tasks, Links, Contacts, and General Discussion. To view any of those lists in Excel 2003:
animal 5-18. Viewing the Shared Documents list in ExcelExcel imports the documents in the list as hyperlinks . To open one of the files, click on the file name in the worksheet. The list in Figure 5-18 is linked to the Shared Documents list in SharePoint. If a member adds a new document to the workspace, you can see that change in Excel by clicking one of the buttons on the Lists toolbar shown in Figure 5-19. Refreshing a list discards local changes and updates the (local) worksheet list with data from the SharePoint server. Synchronizing updates both the worksheet list and the SharePoint list. In the case of the Shared Documents list, the two buttons in Figure 5-19 do the same thingall columns in the Shared Documents list are read-only when imported to Excel. SharePoint defines constraints for lists and those constraints are carried over when the list is imported. animal 5-19. Refreshing a shared list in Excel5.3.2. Importing Lists into Existing WorkbooksThe preceding section showed you how to import a shared list into a new Excel workbook. If you import a list into an existing workbook, Excel deletes all of the Visual Basic code and ActiveX controls contained in the workbook, so if your workbook contains macros, they will stop working. You can avoid this problem by importing the list using an Excel macro, rather than doing it from the SharePoint site. The following VBA procedure imports the Products list from a SharePoint site: Sub ImportList( ) Const site = "http://wombat1/Chapter 5 Samples" Const list = "Products" Dim ws As Worksheet Dim src(1) As Variant Set ws = ThisWorkbook.Worksheets(1) src(0) = site & "/_vti_bin" src(1) = list ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1") End Sub 5.3.3. Editing Lists and Reconciling ChangesThe Shared Documents list is read-only, but other types of shared lists can be imported into Excel, edited, then synchronized to send the changes from Excel back to SharePoint. To see how this works, repeat the preceding procedure with the Contacts list. Once imported into Excel, you can change contact information; then click Synchronize to update SharePoint. As with workspaces, more than one member can edit a shared list at the same time. If your changes conflict with another member's changes, Excel displays the dialog box in Figure 5-20 to resolve the conflicts. To replace the other member's changes with yours, choose Retry My Changes. 5.3.4. Creating and Sharing Lists in ExcelThe built-in SharePoint lists aren't really the best examples of the types of lists you work with in Excel. I used them to introduce shared lists because they are a quick animal 5-20. Reconciling conflicting edits in a shared listway to show you how some key features work. It's much more useful to create a list in Excel first and then share that list through SharePoint. To create a list from Excel:
Excel calls sharing a list publishing . 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 5-21). When a list is published, SharePoint creates a new item in the Lists folder of the SharePoint site that members can use to view or modify the list's data. The list doesn't appear on the home page of the site by default, so there's no obvious way for other members to get to the list. To add the list to the Quick Launch bar on the home page:
|