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.
Dear KB,
ReplyDeleteThank 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
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.
ReplyDeleteHi Brandon, yes, it's possible, but you have to rely on 3rd party tools such as http://www.softfluent.com/product/sharePoint-list-synchronizer/
ReplyDeleteI 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!
ReplyDeletehttps://www.cdata.com/drivers/sharepoint/excel/
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.
ReplyDeleteSharepoint Training in Chennai
This is not working with Excel 2016. where to get add-in (SynchorinizeWSSandExcel) for excel 2016 to sync it with sharepoint list
ReplyDeleteThis is very great thinks. It was very comprehensive post and powerful concept. Thanks for your sharing with us. Keep it up..
ReplyDeleteOracle Training in Chennai | Oracle Training Institutes in Chennai
They had an interesting debate over whether or not the product is a scam or not. Developers didn't spend any time developing them and they were just thrown onto the Amazon marketplace. If you are curious to know more about regal assets review, read me.
ReplyDeleteWhen it comes down to it, there are many different print marketing options out there, but there are only a few good options that will save you money. Make sure you do your research and find out which ones will be the best for your business. You are curious to know more about promarketing trend, browse this site.
ReplyDeleteInnovations and inventions are the main purpose of technology transfer. A new product or service is a combination of the invention and the application of the technology. If you want to get more interesting details about tech solution, you may go online.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete