Click here to Skip to main content
Click here to Skip to main content
Go to top

Restoring Two-Way Synchronization on SharePoint Lists Using Excel

, 6 Dec 2010
The SharePoint List Synchronizer for Excel 2007 and 2010, presented here, allows Excel users to open SharePoint Lists in two-way sync in 2007 and 2010 versions. This very powerful feature used to work directly in Microsoft Office Excel 2003 but the update ability was deprecated in 2007.

Editorial Note

This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

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 and Microsoft Office Excel 2010, the ability to update the information in SharePoint lists from Excel is deprecated. Instead, Microsoft Office Access is the recommended platform for writing data to SharePoint lists and for using lists offline.

However, the SharePoint List Synchronizer for Excel 2007 and 2010, presented here, addresses this issue and allows Excel users to open SharePoint Lists in two-way sync.

image001.png

Overview

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

  • Opening a list from SharePoint with Excel 2007 or 2010 disables the "Synchronize with SharePoint" feature.
  • Existing .XLS files from Office Excel 2003 still load and continue to support update functionality when opened in Excel 2007 or 2010. 
  • Lists that link to a SharePoint site are converted to read-only tables when you save existing files to the new Open XML Excel 2007 or 2010 file formats. 

Meanwhile, Microsoft published an add-in which allows users to publish new read-write lists in SharePoint (Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists). While this add-in works great to publish new Excel tables to SharePoint, it doesn’t address the issue of lists exported from SharePoint. For example, every time you export your SharePoint list to Excel 2007 and 2010; all changes applied in Excel cannot be synchronized back with the original list anymore, instead you need to apply those changes manually or use Microsoft Office Access. The "Synchronize with SharePoint" menu has disappeared:

image002.png

Well, thanks to the SharePoint List Synchronizer for Excel 2007 and 2010, this scenario is now possible in Excel again: you can export your list to Excel 2007 and 2010, and synchronize back to SharePoint without any extra-effort!

Example

  • With the tool installed, go to your favorite SharePoint site and go to a SharePoint List.

image003.jpg

  • Click on "Export to Excel".

image004.png

  • Click on "Open", and Excel will open the list however this time with the "Synchronize with SharePoint"option activated!

image005.png

Note: In order to retain the functionality for this table across saves, you need to save the workbook in the Excel 2003 .XLS file format.

Basic Authentication over Non-SSL HTTP Connections

By default, Microsoft Excel 2010 doesn’t prompt for user name and password when accessing basic authentication over non-SSL (HTTP instead of HTTPS) protected sites.

Therefore, when trying to access a list on one of those access protected sites results in the "You do not have adequate permissions to modify this list. Changes to your data cannot be saved."Error as shown in the screenshot below:

image006.png

The SharePoint List Synchronizer for Excel supports such scenario thanks to its "Basic authentication over non-SSL connections"feature.

image007.png

By activating this feature through our tool, Excel will now prompt users for their credentials and pass them to the site. This way, Excel 2010 users will be able to open SharePoint Lists from access protected sites in two-way sync over again.

Useful Links

SoftFluent’s SharePoint List Synchronizer for Excel 2007 and 2010:

Microsoft’s Excel 2007 Add-in:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

carl.b.anderson
Software Developer SoftFluent
France France
Carl Anderson is a .NET consultant at SoftFluent. You can contact him at carl "dot" anderson "at" softfluent "dot" com.

Comments and Discussions

 
QuestionData from Excel to SharePoint List PinmemberAASHUTOSH ACHARYA31-Mar-14 4:32 
QuestionWorkarounds Pinmembervinodajacob26-Nov-13 4:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 6 Dec 2010
Article Copyright 2010 by carl.b.anderson
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid