Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
Office Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
Office Development
Office Talk
2007 Office System
 Publishing and Synchronizing Excel ...

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (0)
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

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

  1. Download and install the Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists.

  2. Start Office Excel 2007.

  3. Click the Microsoft Office Button, click Excel Options, and then click the Add-Ins tab.

  4. Select Excel Add-ins in the Manage drop-down list and click Go.

  5. In the Add-Ins dialog box, click Browse, navigate to the Excel 2007 SharePoint List Synchronizing Add-in, and then click OK.

  6. 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
    Selecting the Rwsplist option

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

  1. 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.

  2. Specify a file name, and then click Save.

To publish the table to a SharePoint list

  1. Highlight the table. The Table Tools label is displayed above the Design tab.

  2. Click the Design tab.

  3. In the SharePoint group, click Publish and allow Sync.

    Figure 2. The Publish and allow Sync button on the Design tab
    Publish and Allow Sync button on the Design tab

  4. 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
    Exporting a table to a SharePoint R/W List

  5. In the Name text box, type in the name of the list and, optionally, type a description for the list.

  6. Click Publish. Navigate to the page that you specified earlier in SharePoint site and verify that the list published.

To update the SharePoint list

  1. In the table in the workbook, make the changes to the data.

  2. With the pointer in the table, right-click, point to Table, and click Synchronize with SharePoint.

    Figure 4. The Synchronize with SharePoint option
    Selecting the Synchronize with SharePoint option

  3. 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

Community Content   What is Community Content?
Add new content RSS  Annotations
How to Sync Excel 2007 with existing wss 3.0 list      Barry IT99 ... Junyan He   |   Edit   |   Show History

1. Thank you for creating add-in. We users all over the world using excel for offline data input to SharePoint lists.

2. I have installed the Add-In and have some questions:

a. I am confused how to use this add-in with existing lists. When I enter an existing list into the Publish dialog, I get an error message about the List already existing and recommends that I should rename the list.

b. I changed the publish macro by commenting out the publish logic. This allows me to sync with existing lists.

' If ListDescription.Value = "" Then
' retUrl = ActiveCell.ListObject.Publish(Array(ServerName.Value, ListName.Value), False)
' Else
' retUrl = ActiveCell.ListObject.Publish(Array(ServerName.Value, ListName.Value, ListDescription.Value), False)
' End If

' If retUrl <> "" Then
ActiveCell.ListObject.Delete
ActiveSheet.ListObjects.Add xlSrcExternal, Array(fullServerName, ListName.Value), True, xlYes, listPoint
' Else
' MsgBox "There was an error during publish, please check the server name"
' End If
Unload ExportRWList

3. could you please comment your code

4. could you provide the full source code, I do not understand how you are adding the sync item to the table drop down list and what code is firing when you select that item.

Noted by Junyan He:The server name's sample is "http://myserver:82",and the list name's sample is "50EE7790-5768-4AB7-B33D-FCCC09D5E5E2".The list name can be copied from Data Connection Attributes-Definition-Command Text.To get detail information please see KB930006 http://support.microsoft.com/kb/930006/en-us.

Run time error      mareza ... silsbeel   |   Edit   |   Show History

Thanks for the add-in. I already downloaded and installed the add-in, saved the workbook in the 97-2003 format, tried publishing the table to a Sharepoint site, and got the following error "Run time error '-2147467259 (80004005)'. An unexpected error occurred. Changes to your data cannot be saved." Can you help me out on this? Looking forward to your replies. Thanks

Did you check the address that you were publishing to? I think that you need to be at the site level "breadcrumb" such as http://MySite not http://MySite/dev.aspx

Tags What's this?: excel (x) Add a tag
Flag as ContentBug
Doesn't work for existing lists      Spevaforis   |   Edit   |   Show History
This is not helpful for when an existing list is required or the person updating excel does not have rights to create lists. Really need the full Excel 2003 functionality back pls.
This just does not work      AdrianJMartin   |   Edit   |   Show History
still get the same errors - it's so frustrating, import/synchronising data just does not work and as usual there are no helpfull errors messages to explain why?
This is working only in the development server      Senthilrajan Kaliyaperumal   |   Edit   |   Show History

This is working in the developemt server but not working in ther web server. I tried the same steps from my local computer to web server through internet but it is giving run time error(Run time error '-2147467259 (80004005). So that I am not able to connect to the server.

Please give some solution on this.

Tags What's this?: Add a tag
Flag as ContentBug
This is working in the developemt server but not working in ther web server      Senthilrajan Kaliyaperumal   |   Edit   |   Show History
This is working in the developemt server but not working in ther web server. I tried the same steps from my local computer to web server through internet but it is giving run time error(Run time error '-2147467259 (80004005). So that I am not able to connect to the server.

Please give some solution on this.
This is still not working      Bert vanDee   |   Edit   |   Show History

i have installed everything everything, but not extra functioanlity.

What a mess to remove excel functionlity. Access is not the way to upgrade.

also, most of my users don't have Access, but only Excel.

when will this be solved?!?!?!?

Tags What's this?: Add a tag
Flag as ContentBug
The date field export error      Dzumbu   |   Edit   |   Show History

I installed the plug-in and its working extremely well. Thanks to the dreator of this plug-in.

Now my problem is that when i publish the list to sharepoint a field i have wich is of a date format looses the date property and on Sharepoint it is loaded as a string.

The thing is i want to use the list i publish to sharepoint to create a calendar view and i need the dates fields from the list.

ANybody please help.

thank you

Tags What's this?: Add a tag
Flag as ContentBug
Could Have More      cookieking   |   Edit   |   Show History
I was excited to see that the sync function was brought back, but reading through this is appears to be disappointing. I also agree we need the full sync functionality of excel 2003. I have more of a need to sync existing lists then to publish new. Also when I followed the steps I do not have all the menu items in figure 4. The Synchronize with SharePoint option. Something missing?
Tags What's this?: Add a tag
Flag as ContentBug
Useless Add-In      richduffy   |   Edit   |   Show History
I too am very disappointed with this add-in. I have created my list in MOSS, and wanted to export to Excel for quick data entry. Having to then publish my list as a NEW list, which loses all the column types i.e. Date columns become text, dropdown choice become text, etc.. makes this add-in worthless.

I can understand that Access for data entry is a better fit, but as already pointed out, not everyone has Access installed, therefore by removing this functionality from Excel 2007 Microsoft is using it's position to force users to purchase Access if it is not part of their Office suite.

This worked perfectly in Excel 2003!
Tags What's this?: Add a tag
Flag as ContentBug
Updating Existing SharePoint Lists with Excel 2007      Becky_Finnick   |   Edit   |   Show History
Delete me -- something weird happened when I attempted to submit my comment.
Flag as ContentBug
Updating Existing SharePoint Lists with Excel 2007      Becky_Finnick   |   Edit   |   Show History
Delete me -- something weird happened when I attempted to submit my comment.
Flag as ContentBug
Updating Existing SharePoint Lists with Excel 2007      Becky_Finnick   |   Edit   |   Show History
Delete me -- something weird happened when I attempted to submit my comment.
Tags What's this?: Add a tag
Flag as ContentBug
Updating Existing SharePoint Lists with Excel 2007      Becky_Finnick   |   Edit   |   Show History
Delete me -- something weird happened when I attempted to submit my comment.
Flag as ContentBug
Updating Existing SharePoint Lists with Excel 2007      Becky_Finnick   |   Edit   |   Show History

Delete me -- something weird happened when I attempted to submit my comment.

Flag as ContentBug
Customers don't want to Down Rev back to Excel 03      Becky_Finnick   |   Edit   |   Show History

Dev. Team Help: Please put the code for Data functionality into Excel 07 or we will have to down revision Excel.

This is a huge miss on either the SharePoint Services team and/or the Excel 2007 team. I need to be able to copy and paste column and row content to new and existing lists. Plus perform calculations and update the list.

Flag as ContentBug
ID Column Lock-Out      PC from DC   |   Edit   |   Show History

I am pulling Data from my SQL server, which has its own ID, and crunching a bunch of data. i would like to use the SQL ID for Sync to SharePoint. Is there a way to unlock the ID Column so that I can change column numbers?

Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker