Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists (From MSDN)

cxwpf200

Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists

　　Summary: In Microsoft Office Excel 2007, the ability to synchronize the data between a table and a list in Microsoft Windows SharePoint Services is deprecated. This article describes an add-in that enables you to update the information in a SharePoint list from Excel 2007.
　　Jon Adams, Microsoft Corporation
　　Frank Rice, Microsoft Corporation
　　June 2007
　　Applies to: Microsoft Office Excel 2007, Microsoft Windows SharePoint Services 3.0
　　Download Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists.
　　Contents

• 　　Overview
  
• 　　Updating SharePoint Lists in Excel 2007
  
• 　　Installing the Excel 2007 SharePoint List Synchronizing Add-In
  
• 　　Publishing and Synchronizing Table Data to a SharePoint Site
  
• 　　Conclusion
  

Overview

　　In Microsoft Office Excel 2003, you can connect to (read from) and update (write to) lists that reside on SharePoint sites. This allows you to keep the information in your Excel 2003 tables synchronized with the information that appears on the SharePoint site.
　　In Microsoft Office Excel 2007, the ability to update the information in SharePoint lists from Excel is deprecated. Instead, Microsoft Office Access 2007 is the recommended platform for writing data to SharePoint lists and for using lists offline.
Updating SharePoint Lists in Excel 2007

　　In Office Excel 2007, the ability to update SharePoint lists changed in the following ways:

• 　　Existing files from Office Excel 2003 load and continue to support update functionality when opened in Excel 2007.
  
• 　　Lists that link to a SharePoint site are converted to read-only tables when you save existing files to the new Open XML file formats.
  
• 　　It is still possible to publish a table to SharePoint lists; however, this is a one-time write operation to Microsoft Windows SharePoint Services 3.0.
  

　　Before you despair, Jon Adams created an Office Excel 2007 add-in, Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists, which adds a button to the Table Tools tab on the Office Fluent Ribbon. This add-in allows you to publish a read-write list to Windows SharePoint Services. Note that you cannot save the workbook in the new Office Open XML Formats. Instead, to retain the functionality, you need to save the workbook in the Excel 97-2003 (Biff8) file format.
Installing the Excel 2007 SharePoint List Synchronizing Add-In

To install the add-in for use in Office Excel 2007

• 　　Download and install the Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists.
  
• 　　Start Office Excel 2007.
  
• 　　Click the Microsoft Office Button, click Excel Options, and then click the Add-Ins tab.
  
• 　　Select Excel Add-ins in the Manage drop-down list and click Go.
  
• 　　In the Add-Ins dialog box, click Browse, navigate to the Excel 2007 SharePoint List Synchronizing Add-in, and then click OK.
  
• 　　In the Add-Ins dialog box, verify that the SynchronizeWSSandExcel option is selected and then click OK.
  
  
  　　　　Figure 1. The Add-Ins dialog with the Rwsplist option selected
  　　
  
  
  

Publishing and Synchronizing Table Data to a SharePoint Site

　　Using the add-in is simple, as you see in the following steps.
To add the add-in to a workbook

• 　　Save the workbook in the Excel 97-2003 (BIFF8) file format by clicking the Microsoft Office Button, pointing to Save As, and then clicking Excel 97-2003 Workbook.
  
• 　　Specify a file name, and then click Save.
  

To publish the table to a SharePoint list

• 　　Highlight the table. The Table Tools label is displayed above the Design tab.
  
• 　　Click the Design tab.
  
• 　　In the SharePoint group, click Publish and allow Sync.
  
  
  　　　　Figure 2. The Publish and allow Sync button on the Design tab
  　　
  
  
  
• 　　In the Export Table to SharePoint R/W List dialog box, in the Address text box, type in the page address on the SharePoint site where you want to publish the list.
  
  
  　　　　Figure 3. The Export Table to SharePoint R/W List dialog box
  　　
  
  
  
• 　　In the Name text box, type in the name of the list and, optionally, type a description for the list.
  
• 　　Click Publish. Navigate to the page that you specified earlier in SharePoint site and verify that the list published.
  

To update the SharePoint list

• 　　In the table in the workbook, make the changes to the data.
  
• 　　With the pointer in the table, right-click, point to Table, and click Synchronize with SharePoint.
  
  
  　　　　Figure 4. The Synchronize with SharePoint option
  　　
  
  
  
• 　　In the browser, refresh the list and observe that the list reflects the changes.
  

Conclusion

　　Although the ability to update SharePoint lists from Office Excel 2007 is deprecated in favor of publishing and synchronizing lists using Office Access 2007, you can use the Excel 2007 SharePoint List Synchronizing Add-in to update SharePoint lists from Office Excel 2007.
Additional Resources