SharePoint List Synchronization with Excel

A question which is often asked is how you can synchronize a SharePoint list with Excel. The answer differs on what version of Excel you use.

Excel 2003 with SharePoint 2007/2010

With Excel 2003 the synchronization works in both directions. This means that you can create a SharePoint list connect to Excel, make changes in Excel and write back to Excel. The single restriction is that you can't create new items in Excel but modify existing ones.

Excel 2003 has a bug in which an alert pops up and says that the field is read-only, when you try to modify a field. There is a patch for this bug found here: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=952608

To synch the lists go to the list you want to synch and click on "Export to Excel" button in the ribbon.



The list will be opened in Excel and automatically connected to the SharePoint list. Now you can make changes within the Excel sheet. To synch back click on "Data" >> "Lists" >> "Synchronize Lists". Ensure yourself that a cell within the table is clicked. Otherwise the "Synch Lists" menu item is disabled.


Excel 2007 with SharePoint 2007/2010

With Office 2007 Microsoft has choosen Access to be the tool for synchronzation with SharePoint. You can still create a connection from Excel to a SharePoint list, but the synchronization is one-way from SharePoint to Excel.

If you still want a two-way synchronization you can download a free Add-In from Microsoft. With this tool you have to create a list in Excel, publish it to a SharePoint list and synchronize with it. But it not addresses the issue to synchronize with an existing SharePoint list

There is also a Add-In from third party organization: http://www.softfluent.com/products/sharepoint-list-synchronizer. With this tool you can also synchronize with existing SharePoint lists.


Excel 2010 with SharePoint 2007/2010

Excel 2010 offers like Excel 2007 a one-way synchronization. You have the use Add-Ins to synchronize two-way with SharePoint. The better way is to use Access 2010 which can be synchronized with a SharePoint-List and then exported to a Excel list or the way back.

It seems that the MS Add-In works also fine with SharePoint 2010.  (See:  http://sp.meetdux.com/archive/2010/03/30/Sync-Excel-SharePoint-2010.aspx )

Comments

Richard said…
Your blog about the "SharePoint List Synchronization with Excel" helped me a lot. Thank you! Baris
April 3, 2012 at 11:16 AM
Anonymous said…
If you want to update or synchronize your Excel document from within a SharePoint workflow, then you may look at ready to use third-party actions to set or get cell values: http://www.harepoint.com/Products/HarePointWorkflowExtensions/Office-SharePoint-Workflow-Action.aspx
September 2, 2013 at 11:42 AM
Post a Comment

Popular posts from this blog

Open SharePoint 2010/2013 Display, Edit, New Forms in Modal Dialogs

Image
If you want to open a SP 2010 or in SP 2013 list item in a modal dialog you have two choices. Using the standard JavaScript functions or creating own functions. How to find which item URL is called If the dialog box appears you don't have an address bar to pick up the called URL. To see which URL is called when you edit or open a list item use the Internet Explorer Developer Tools. Open your source list in the Internet Explorer. Press F12 to open the developer tools. Click on the "Network" tab and then on "Start Capturing" Now click the link you want to track. You'll see the requests listed and you can also copy the urls for your need. SharePoint usually calls the /_layout/listform.aspx with the list parameter. The form then redirects to the appropriate list form. Method 1: Using core functions When using SharePoint 2010 core javascript functions to open elements in dialogs mostly you'll need to know : the ID of the l
Read more

Ways to redirect http requests in SharePoint

Image
The are multiple szenarios when you need a redirect in SharePoint: You've migrated SharePoint into a new URL (e.g http://sp2010 to http://sp2013) You moved a site collection You moved a sub site into a new site collection If you want to redirect in SharePoint without custom solutions you have the following options: Redirect with XML Viewer or Content Editor WebPart You can redirect with JavaScript by inserting a WebPart into the site and adding simple JavaScript. This is easy to configure but redirects only the site in which the WebPart sits (Probably the Mainsite). If users have saved links the their document libraries this method fails, unless you put the same WebPart into the document library. So go to the site you want to redirect and the XML Viewer WebPart. Open the WebPart settings and add the following line of code: <script type="text/javascript"> location.href = "<<the url you want to redirect>>"; </s
Read more

Using WebDav on IIS 7 to access UNC Paths

Image
In this post I want to show you step by step how to connect to a file share using webdav on IIS 7. For this post I assume: that you have already an IIS installed on the server  that you have installed WebDav or enabled the WebDav Feature on IIS. the server on which is IIS installed can reach the file share. 1: CREATE APPLICATION POOL The first step is to create a seperate application pool on the IIS. Open the program "Internet Information Services Manager". Click on "Application Pools" Click on "Add Application Pool" on the right menu Name it "WebDav". Let the rest as it. Right click on the new created Application Pool "WebDav" and select "Advanced Settings". Pay attention to the following attributes: .Net Framework Version : No Managed Code Identity : The identity of the Application Pool should be an account which has permissions to read the file share. Load User Profile : Should be set to "False"
Read more