Wednesday, April 16, 2014

Excel Add-in that can publish and sync Excel tables to SharePoint (2007/2010/2013/O365) Lists

I have some project tracking related information on my excel worksheet and i wanted to store that in a SharePoint List. I wanted to find out any Add-in available in Excel, which will upload the data into SharePoint and synchronizes it in. I browsed and found that  WSSandExcel (SynchorinizeWSSandExcel.xlam) Add-in helps in synchronizing Excel tables with SharePoint Lists on SharePoint 2010/2013 and Office 365.

This was originally built for Excel 2007 for WSS/Moss 2007, but the good news is,  it works well for SharePoint 2010, SharePoint 2013 and O365 

You can download the plug-in from here. Follow the installation instructions given in that page.

How to use this Add-in with Excel 2013.

Spin up the Excel 2013(The usage will be similar for Excel 2007/2010.). Choose this Add-in under options -> Add-ins. You have to enable the Macros for the Add-ins to work.

How to Publish an Excel table in SharePoint 2010/2013/O365.

Step1: Create a table of data in a worksheet and click on Design tab. You can see "Publish and Sync" in the menu bar appearing after installing the Add-in. This is only to create a SharePoint List.
Step 2: Click on that  will bring a dialog box "Export Table to R/W SharePoint List". For O365 SharePoint do not forget to enter "Description" , other wise the Add-In will fail. 
Fill out the details and click on publish. For O365 based SharePoint lists you will be prompted for credentials. 
Go to your site and check the Add-in would have created the SharePoint List. 

How to use this Add-in to Sync data from SharePoint to Excel and Vice Versa.

After adding a List Item, go the excel worksheet -> right click on the Table data -> Table >Click on Synchronize with SharePoint. You can see the newly added/edited list item in your excel table.
You can also add/edit items in the excel table and synchronize with SharePoint by following the same procedure given above. This Add-in greatly helps in quickly creating a SharePoint List , but there are some limitations as well. 

Saving the Excel.

The Excel sheet has to be saved as in Excel 97 – 2003 Workbook(*.xls) format. If you save it other formats, you will not be able to sync the list items as "Synchronize with SharePoint" option will not appear.


Limitations of this  Add-in.

  1. Enabling  Macros
  2. It will not work for O365 , if List description is empty(Step 2)
  3. Excel Workbook has to be saved as in Excel 97 – 2003 Workbook(*.xls) format.
  4. SharePoint Specific columns like Person/Group Column, Managed Metadata, Lookup etc., cannot be created.
  5. Not a complete business solution.


I hope this post is helps you.

5 comments:

  1. Dear KB,

    Thank you for your great post!
    I am considering to use this technique (with some PowerPivot in the background) for our business. I would be very interested in why you consider it as "not a complete business solution" besides the limitations that you already named.

    Thank you for your answer!
    Best,
    Max

    ReplyDelete
  2. Is there a way to setup this connection without having to publish the list from Excel to SharePoint first? I'd like to export the list out of SharePoint and then be able to sync back and forth as needed. My list uses the Contacts content type and if I publish the list from Excel to SharePoint in order to setup the connections then I can use that content type.

    ReplyDelete
  3. Hi Brandon, yes, it's possible, but you have to rely on 3rd party tools such as http://www.softfluent.com/product/sharePoint-list-synchronizer/

    ReplyDelete
  4. I just wanted to add an additional source of Excel Add in for SharePoint that is a really cool: The Cdata SharePoint Excel Add-In allows you to connect with live data from SharePoint and SharePoint Online, directly from Microsoft Excel. Use Excel to read, write, and update SharePoint Lists, Contacts, Calendar, Links, Tasks, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

    https://www.cdata.com/drivers/sharepoint/excel/

    ReplyDelete
  5. Great post! I am see the programming coding and step by step execute the outputs.I am gather this coding more information. It's helpful for me my friend. Also great blog here with all of the valuable information you have.
    Sharepoint Training in Chennai

    ReplyDelete