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.

Saturday, April 12, 2014

Office 365, Sharepoint Online and SharePoint 2013 on-prime feature comparison

My client is now pursuing options for migrating to O365 and wants to have a hybrid model for the
SharePoint Environments. I was looking for detailed descriptions of the services and features and I found this link . Andrew Connell's excel sheet was also helpful for us.

I am sure this would help you to understand the difference in feature offerings available for O365, SharePoint Online and SharePoint 2013 on-prime.