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.

22 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
  6. This is not working with Excel 2016. where to get add-in (SynchorinizeWSSandExcel) for excel 2016 to sync it with sharepoint list

    ReplyDelete
  7. If you should use an internet proxy many times, think about upgrading from a completely free proxy to a paid proxy service plan which offers higher performance and perhaps greater quality of service guarantees. Interested to know more about free proxy? visit here.

    ReplyDelete
  8. Output controls, on the flip side, directly limit the quantity of fish that can be taken from the water. Electronic systems are typically the very best. If you want to get more interesting details about output management platform, go here.

    ReplyDelete
  9. The rationale for censorship differs for many kinds of data censored. To get more detailed info on pirate bay proxy, visit on hyperlinked site.

    ReplyDelete
  10. This is very great thinks. It was very comprehensive post and powerful concept. Thanks for your sharing with us. Keep it up..
    Oracle Training in Chennai | Oracle Training Institutes in Chennai

    ReplyDelete
  11. The phone is extremely slippery as a result of the glass back so covering it using a great case would be a superior selection. It also offers enormous storage space and good connectivity features. If you are curious to know more about best phone under 10000, visit this site.

    ReplyDelete
  12. RoR developers seem to be putting a whole lot more emphasis on REST style Web services for future improvement. If you want to get more interesting details about ruby on rails maintenance, click here now.

    ReplyDelete
  13. It's possible to even try the fit and search for the exact same fit online and look for some discounts or offers on several different mens briefs and undies.

    ReplyDelete
  14. Many schools have a digital inclusion curriculum that is designed to teach students the latest in technology. More information about computer & laptop repair service click here.

    ReplyDelete
  15. There are so many different types of electronic stores that you can use to manage and sell your products and services, including Deep Shopify. Interested to know more about shopify review? find out here.

    ReplyDelete
  16. There are many ways to earn and that includes utilizing affiliate marketing. Take time to read Super Affiliate System review to get to know how you will promote your products and services through this program. It allows you learn more at the same time.

    ReplyDelete
  17. 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.

    ReplyDelete
  18. When 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.

    ReplyDelete
  19. Innovations 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.

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete