Section 5.3. Sharing Lists

5.3. Sharing Lists

In 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:

• Lists automatically add column headers to the range.

• Lists display a handy List Toolbar when selected.

• It is easy to total the items in a list by clicking the Toggle Total button.

• XML data can be imported directly into a list.

• Excel can automatically check the data type of list entries as they are made.

• Lists can be shared and synchronized with team members through SharePoint.

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 Excel

There 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:

1. Display the list in the browser. For example, select Shared Documents from the workspace home page .

2. Click Export to Spreadsheet. SharePoint creates an Excel query and displays the File Download dialog box .

3. Click Open to display the query results in Excel. Excel displays a security warning .

4. Click Open to run the query. Excel displays the Import Data dialog .

5. Excel creates a new workbook, inserts the list, and displays a list of limitations, as shown in Figure 5-18 .

Other versions of Excel also allow you to import lists, but the results vary. See Appendix B for a table of version differences.

animal 5-18. Viewing the Shared Documents list in Excel

Excel 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 Excel

5.3.2. Importing Lists into Existing Workbooks

The 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 Changes

The 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 Excel

The 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 list

way 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:

1. Select a range of cells and then choose Data List Create List. Excel displays the Create List dialog .

2. Excel converts the selected range into a list and displays the Lists toolbar .

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:

1. Display the list in the browser. To do that, either click the link in the last dialog box (Figure 5-21) or select the list in Excel and choose Data List View List on Server .

2. Change general settings.
3. 
   

   

   There are other ways to view lists not displayed in the Quick Launch bar. For example, choose Documents and Lists Lists; then choose the list to display. The Quick Launch bar is for things members use every day.