Home Library Learn Downloads Support Community
Sign in | United States - English | |
87 out of 125 rated this helpful - Rate this topic

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

Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
More or less
I would need it the other way round. I would like to make changes in the Excel-Sheet (eg. a CSV-File that comes at regular intervals) and that should be synced to Sharepoint. With this Add-In it's not even possible to delete rows in the Excel-Sheet because of the locked ID column that comes after the first sync.
History
Ditto
Completely different menu items.
History
Useless Add-In
I dont think Access is also a best replace to it. You are unable to import Validations or Macros from Access/Excel to sharepoint.

History
Almost useful
I need to run this to sync the same spreadsheet to the same SharePoint list on multiple PCs. $0$0 $0 $0Is there a way to point this at a particular ODC connection so it won't always try to replace the current list and trash the views that were applied on SharePoint?$0
History
WOW!
This was completely useless.
From the screen shot I don't see the options displayed on the right click within the menu.
History
WOW!
This was completely useless.
History
damned
"to retain the functionality, you need to save the workbook in the Excel 97-2003 (Biff8) file format."

GRRRR !!
History
Taking away functionality
Why take away the one tool that everyone knows how to use, Excel; and require people to use another, Access, that very few know how to use.  Typical Microsoft, if you want to make good use of their product, you have to be a developer or hire one; screw the power user.
Thanks, thanks a lot...
History
Ashamed to say
I want to be a MS fan but frankly this just wasted more of my time.  Little wonder your competition is outgunning you at this time.  How about giving us something that actually works in more than just your MS labs?
History
Thank you...works great!
After I figured out exactly were the execution file had downloaded to on C:, it was a breeze to add it in.  Saved me lots of data entry time lost. Thank you again.
History
Just doesn't work
  1. I've tried eveything it seems....2007, 2003, WSS , Moss....none of these addons work to get excel data into Sharepoint.
Hopefully this will get fixed properly in 2010 beta.
History
Useless add-in

There is no "Browse..." in Publish button, user has to type the path as is if it is still 1989. On top of this the add-in does not work.
History
ID Column Lock-Out

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?


[tfl - 10 12 09] Hi - and thanks for your post. You should post questions like this to the MSDN Forums at http://forums.microsoft.com/msdn or the MSDN Newsgroups at http://www.microsoft.com/communities/newsgroups/en-us/. You are much more likely get a quicker response using the forums than through the Community Content. For specific help about:
.NET Framework : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.dotnet.framework
PowerShell     : http://groups.google.com/group/microsoft.public.windows.powershell/topics?pli=1
SQL Server     : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.sqlserver%2C&
Visual Studio  : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.vstudio%2C&
Windows        : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.windows%2C&
All Public     : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public%2C&

History
This is working only in the development server

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.


[tfl - 10 12 09] Hi - and thanks for your post. You should post questions like this to the MSDN Forums at http://forums.microsoft.com/msdn or the MSDN Newsgroups at http://www.microsoft.com/communities/newsgroups/en-us/. You are much more likely get a quicker response using the forums than through the Community Content. For specific help about:
.NET Framework : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.dotnet.framework
PowerShell : http://groups.google.com/group/microsoft.public.windows.powershell/topics?pli=1
SQL Server : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.sqlserver%2C&
Visual Studio : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.vstudio%2C&
Windows : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.windows%2C&
All Public : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public%2C&

History
This just does not work
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?

[tfl - 10 12 09] Hi - and thanks for your post. You should post questions like this to the MSDN Forums at http://forums.microsoft.com/msdn or the MSDN Newsgroups at http://www.microsoft.com/communities/newsgroups/en-us/. You are much more likely get a quicker response using the forums than through the Community Content. For specific help about:
.NET Framework : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.dotnet.framework
PowerShell : http://groups.google.com/group/microsoft.public.windows.powershell/topics?pli=1
SQL Server : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.sqlserver%2C&
Visual Studio : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.vstudio%2C&
Windows : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.windows%2C&
All Public : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public%2C&

History
This is still not working

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?!?!?!?


[tfl - 10 12 09] Hi - and thanks for your post. You should post questions like this to the MSDN Forums at http://forums.microsoft.com/msdn or the MSDN Newsgroups at http://www.microsoft.com/communities/newsgroups/en-us/. You are much more likely get a quicker response using the forums than through the Community Content. For specific help about:
.NET Framework : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.dotnet.framework
PowerShell : http://groups.google.com/group/microsoft.public.windows.powershell/topics?pli=1
SQL Server : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.sqlserver%2C&
Visual Studio : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.vstudio%2C&
Windows : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.windows%2C&
All Public : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public%2C&

History
Worthless
Microsoft this is worthless for everyone who has existing lists. Get your act together on synchronising. Do the right thing and add the functionality back into Excel to allow synchronisation with existing lists.
History
For restoring Excel 2003 synchronization feature in Excel 2007 and 2010...
See this article http://www.shahine.com/omar/Excel2007CannotTwoWaySyncToSharepoint.aspx
History
Customers don't want to Down Rev back to Excel 03

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.

History
Could Have More
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?
History
The date field export error

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

History
Run time error

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

History
Doesn't work for existing lists
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.
History
How to Sync Excel 2007 with existing wss 3.0 list

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.

History