Working with Windows SharePoint Services 3.0 from Excel 2007

Article
07/09/2014

Summary: Learn about important changes to Microsoft Office Excel 2007 that allow more powerful analysis of Microsoft Windows SharePoint Services lists. Find out how these changes affect the ability of Excel 2007 to read and write changes to Windows SharePoint Services linked lists. (7 printed pages)

Frank Rice, Microsoft Corporation

March 2007

**Applies to:**Microsoft Office Excel 2007, Windows SharePoint Services

Contents

Background
Changes to Linked SharePoint Lists in Excel 2007
Support for Windows SharePoint Services 3.0 in Excel 2007
Excel 2007 Behavior for Existing SharePoint Read/Write Lists
Excel 2007 Behavior for New SharePoint Read-Only Lists
Using the Excel 2007 Object Model for SharePoint List Operations
Conclusion
Additional Resources

Background

The core services of Microsoft Windows SharePoint Services 3.0 are document libraries and data lists. Lists, as they apply to Windows SharePoint Services, are collections of items that are displayed on a site.

Permissions to access data on a SharePoint site are based on roles in site groups. In Windows SharePoint Services, you can assign permissions to Reader, Author, and Coordinator site groups. Anyone who has access to a SharePoint site can view lists. Every site group except Reader can add items to a list as well as edit or delete items in a list.

Note

You can find more information about Windows SharePoint Services security and roles in the article SharePoint: SharePoint Portal Server Makes Your Intranet More Manageable and Easier to Navigate.

Microsoft Office Excel 2003 can connect to (read from) and update (write to) lists that reside on Windows SharePoint Services sites. By using linked lists, you can combine the data creation and analysis features of Excel with the list management capabilities of Windows SharePoint Services. Excel 2003 lets you create a list and then link it to a Windows SharePoint Services site. Excel users can then directly edit the data in a list, while simultaneously making the data available to all portal users.

Changes to Linked SharePoint Lists in Excel 2007

In Excel 2007, the ability to edit and write to SharePoint lists is deprecated. This change was made because of usability issues that hinder Excel data analysis. Table 1 lists some of these issues.

Table 1. Usability issues with SharePoint lists

Issue	Description
ID column	Every SharePoint list must display the ID column of the source list. This column is never seen on the Windows SharePoint Services side (unless you explicitly specify it). In most cases, there is no reason to display it to the user because it clutters the view of the data. You cannot delete it so if you don't want the user to see the worksheet column, you must hide it.
Inserting or deleting rows	You cannot insert a worksheet row that intersects a list.
Inserting or deleting columns	You cannot remove SharePoint columns or add new ones. Note You can add and remove columns by using query tables.
Data range properties	Not every option in the Data Range Properties dialog box in Excel is supported. The following options are disabled: Refresh Every N Minutes Include Row Numbers Preserve column sort/filter/layout Hiding the header row Insert cells for new data, delete unused cells Insert entire rows for new data, clear unused cells
Headers	The headers cannot be edited.
Cutting and pasting	You cannot cut whole rows because you cannot delete the ID column.
Data validation	You cannot apply Excel data validation rules to SharePoint list cells even if the list does not define any of its own validation rules.
Calculated columns	You cannot change a calculated column formula even though it appears in Excel as a formula.

In addition to these limitations, there is incomplete support for Excel 2003 data types as seen in Table 2.

Table 2. Limited support for SharePoint Services data types in Excel 2003

Data type	Description
Survey matrix	Survey matrix fields do not appear in Excel 2003, which prohibits analyzing survey data in Excel.
Multiple-choice	Windows SharePoint Services formats multiple-choice fields similar to Cats;#Dogs;#Mice. Unless you know that this is the way that Windows SharePoint Services formats multiple-choice fields, the meaning of this formatting is unclear. You can use the Text to Columns option to improve the way this looks in Excel but you might need to reformat it before viewing it in Windows SharePoint Services.
Rich edits	Excel supports only a subset of rich text coming from Windows SharePoint Services. Formatting for tags that are not supported is dropped so you see plain text instead of any special formatting. Tags that are not supported are preserved if you do not modify the field. Editing a rich text field in Excel potentially removes the formatting that was applied outside Excel.
Attachments	Excel does not import or link to attachments.
Document libraries	You cannot add rows to document libraries. However, existing rows can be edited or deleted.
Folders in document libraries	Folders are treated the same as standard documents. Clicking a folder loads Internet Explorer. This takes you to the folder in the document library instead of spawning a new SharePoint list in a new worksheet.

Support for Windows SharePoint Services 3.0 in Excel 2007

Excel 2007 introduces the following changes:

Existing files from Excel 2003 support the updated functionality when opened in Excel 2007.
Lists that are linked to Windows SharePoint Services are converted to read-only tables when existing files are saved to Excel 2007 file formats.
You can still publish a table to Windows SharePoint Services; however, this is performed as a single action to Windows SharePoint Services.

Note

In the 2007 Office system, Microsoft Office Access 2007 is the preferred application for interacting with SharePoint lists offline.

The core focus of Excel is data analysis. To improve data analysis performance, the write-back to SharePoint lists feature is deprecated in Excel 2007. Additionally, eliminating the write-back feature frees Excel from the need to maintain data integrity of the back-end list, so you can analyze SharePoint data in ways that you could not before. For example, you can now perform the following tasks in Excel:

Insert columns anywhere in the list
Rearrange columns in any desired order
Overwrite any piece of data in the list with ad-hoc values
Create calculated columns in the list

These are just a sample of the improved data analysis capabilities that are available in Excel 2007. For information about additional capabilities, see the Help that is included in the 2007 Office system, as well as topics that are available in the Microsoft Office Developer Center.

If you need to use the write-back feature for existing solutions, you can use the Visual Basic for Applications (VBA) object model in Excel 2007 to create read/write lists.

Note

VBA object model support is provided solely to aid transitioning from Excel 2003 to Excel 2007. You should not expect this functionality to exist in future versions of Excel.

Removing support for Windows SharePoint Services write-back scenarios addresses the usability issues described in Table 1. In addition, there is now support for more data types, which enables Excel to be a better consumer of SharePoint data.

To better understand these changes, consider the following scenario:

Janet is a human resources representative at a small company. She is setting up a SharePoint survey to measure employee satisfaction with the company benefits. Windows SharePoint Services gives her some simple results on its Web site, but what Janet really wants is to bring the data into Excel where she can perform more complex analysis and use the data to create a report that she can give to her boss. Fortunately for her, Windows SharePoint Services has an Export to Excel feature. Unfortunately for her, Excel 2003 does not support the essential survey matrix data type. So she upgrades to Excel 2007 because she hears that it has better support for SharePoint data types. Janet is happy to find out that her survey data can now be imported into Excel and she can perform analysis that was not previously possible.

Excel 2007 Behavior for Existing SharePoint Read/Write Lists

Existing read/write lists continue to work as they did in Excel 2003 when opened in Excel 2007.

Functionality

You can still edit and synchronize any read/write lists you created in Excel 2003. The user interface (UI) for this is available in context menus (that is, not in the 2007 Office Fluent Ribbon).

Note

The hide header row ability is disabled for read/write lists.

Data Type Support

There is no added support for SharePoint data types. New data types are ignored by Excel (because of existing Excel 2003 logic), so these types of fields are not seen when working in Excel 2007. In some cases this issue might prevent you from adding or editing records.

Read/Write List Creation

You can no longer create read/write lists from the UI in Windows SharePoint Services or Excel 2007. More specifically, opening an .iqy file that specifies a connection to Windows SharePoint Services creates a read-only list. The "publish and link" functionality has changed and now only creates a read-only link.

Converting Read/Write Lists to Read-Only Lists

Excel 2007 does not provide a way to convert read/write lists to read-only lists, with the exception of Excel 2003 files that are converted to the Excel 2007 file formats.

File Format Conversion

Read/write lists are supported in the Excel binary file format (BIFF8). If you convert a file (either to XML or to BIFF8) to the Excel 2007 format, any read/write lists in that file convert to read-only lists.

Excel 2007 Behavior for New SharePoint Read-Only Lists

All new Windows SharePoint Services–linked lists that are created either through the Excel 2007 UI or using Windows SharePoint Services create read-only lists (they are simplyquery tables in Excel). There are changes in the following areas.

Functionality

With the exception of the special handling of SharePOint data types, read-only lists behave exactly like query tables in Excel 2003. The current View List on Server feature is also supported but is named Open in Browser.

Data Type Support

Read-only lists display all existing and new (Windows SharePoint Services) data types. Excel 2007 treats unrecognized types as text.

Views

Read-only lists show all fields that are represented in the SharePoint view that was exported, and in the same order that the view used. However, Excel 2007 no longer displays the ID field unless it is part of the view.

Using the Excel 2007 Object Model for SharePoint List Operations

Range-based lists, XML lists, list query tables (including read-only lists) and read/write lists are all created by using the ListObjects.Add method. In addition, Excel 2007 adds a new enumeration value (see Table 3) to the xlListObjectSourceType enumeration. This enumeration is used by the SourceType parameter in ListObjects.Add method to signify all read-only query tables.

Table 3. Values of the xlListObjectSourceType enumeration

Value	Use
xlSrcExternal	Read/write SharePoint lists only
xlSrcRange	Range-based lists
xlSrcXML	XML lists
xlSrcQuery	NEW—All other query table connections, including read/write SharePoint lists

Excel 2003 still allows the creation of read/write lists if the current file format is Excel 2007, and alerts the user when saving the file.

The following methods and properties related to read/write SharePoint lists are no longer visible in the Microsoft Visual Basic Editor (VBE) object browser:

ListObject.UpdateChanges
ListColumn.ListDataFormat
ListColumn.SharePointFormula
ListRow.InvalidData

Conclusion

Excel 2007 has improvements and features that make analyzing SharePoint data more efficient and powerful than ever. One outcome of these additions is that Excel 2007 no longer supports write-back to SharePoint lists, because of the lack of rich data types in Excel and the continued innovation of Windows SharePoint Services in this area. In Excel 2007, however, there is a migration path for those users who have created read/write lists in Excel 2003. Also, changes in Access 2007 now make it the premier support for offline SharePoint lists and includes support for rich data types.