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.
- Enabling Macros
- It will not work for O365 , if List description is empty(Step 2)
- Excel Workbook has to be saved as in Excel 97 – 2003 Workbook(*.xls) format.
- SharePoint Specific columns like Person/Group Column, Managed Metadata, Lookup etc., cannot be created.
- Not a complete business solution.
I hope this post is helps you.
