Thursday, August 13, 2015

Sync Excel Tables to a SharePoint List

Update: This doesn't work anymore with the release of Excel 2016. All previous versions of Excel still support it.

I have this requirement as part of a large process automation project for an international company operating in construction. OK, well the requirements never come in that clear - "sync" is not used. The goal is to have a SharePoint 2013 list, populated with data from an Excel 2007 spreadsheet.

Then part of the data would be picked up by a workflow and some of the data will be edited with human interaction during the process. At the end, the data should be in a compatible format to use with a reporting system such as SSRS or PowerPivot for SharePoint Server. That's another topic.

There are several approaches that we can take to achieve the first and most important goal - get the spreadsheet data into a SharePoint list. While the data can be easily exported from Excel (2013) to a SharePoint list quite quickly, there's no way to update it through Excel afterwards. It's a one-off solution which won't add value to this project at all.

I'll just show how that simple option works in Excel 2013 (it works in previous versions, too).

Before starting anything, please have in mind that you can only get the data into a SharePoint list if it's formatted as a table, that won't work for just about any spreadsheet. But we'll start with "any spreadsheet" :) In our scenario, we have a group of employees that might be new hires and we need to get them in a list, so we can possibly start a workflow that will do all the things associated with the new hires - e.g. assign them a telephone number, create an account in Active Directory, etc. The workflow is another topic which we won't discuss, but the main point is that's a common scenario.
Organizations usually transition from paper-based process through Excel spreadsheets to an automated software solution at the end.

Today we are lucky to have 7 new hires across different departments. We got this nice little spreadsheet from HR and we want to create a pilot list called "Employees" in our brand new SharePoint Online environment. It will work in the same way if the environment was SharePoint Server 2013 on-premise anyway.

First we'd format this as a table, in order to be able to even get to the "Export" button:


We now have the Export button, under the "Table Tools" section on the ribbon and we can choose the "Export Section to SharePoint List" option. 


You get prompted for the URL of the SharePoint site and the name of the list that would be created for the table data:


A summary of the fields that will get imported. 


Nice to know is that only one the following types will be assigned to the columns in SharePoint:

  • Text (single line)
  • Text (multiple lines)
  • Currency
  • Date/time
  • Number
  • Hyperlink


If a column has cells with different data types, Excel applies a data type that can be used for all of the cells in the column. For example, if a column contains numbers and text, the data type in the SharePoint list will be text.

Here are the results when that sync finishes. A nice little message in Excel:


A newly created list in our target SharePoint site:

Our employees are ready to jump into the complex workflow process to follow :) More on that will follow in one of the next posts likely if/when we get this project rolling out live. Now it's only a PoC.


More on this basic one-way sync can be found on this Excel 2007 Support article


Now let's talk about the second solution... which will enable us to store the Excel data... and sync it from Excel anytime at a later stage. Out of the box that's not available in Excel or SharePoint.
But there's a good add-in for Excel 2007 (great as our customer is on Excel 2007, and I've found it working well on Excel 2013, too). The add-in was available for Excel 2007 and later on deprecated.
You can still find it here and use it (for free of course) if it matches your organization's needs.

When you download and extract this, you simply get a macro-enabled Excel 2007 workbook called SynchronizeWSSandExcel.xlam. Start that one and enable macros. Then get your table in. Don't forget to format it as a table, as in the previous approach, the same requirement is valid here.

Then go to the Design tab under Table Tools and you'll find a brand new button - "Publish and allow Sync":
When you click it, the dialog box looks similar to the previous one, but it's a 1-step process this time. Doesn't get any sweeter than this (I'll just call my list New Hires as the Employees is already taken):


The outcome is the same as solution 1:



But we now have the ability to change the data... let's say Jack D needs to move from the IT department to Developers, we can change that and then synchronize the changes to the SharePoint list from Excel without the need to even open SharePoint. You just change the cell data, use the "Synchronize with SharePoint" option and your list will be updated.








In one of the next blog posts I'll talk about a couple of other approaches.. which are in fact the a lot more sophisticated and, but involve a significant cost that involves the need of SharePoint Server Enterprise Edition and a 3rd party workflow product or some custom coding to call the Excel Web Services.









20 comments:

  1. What format can we save the excle file as

    ReplyDelete
  2. Hi Rashid,

    The excel could be .xls or .xlsx - both are working for me.

    ReplyDelete
  3. Hi Dimitar,

    Thank for the beautiful post, I am able to follow your second procedure and i have one question, i have created an excel and published into sharepoint 2013 server, Edited excel and updated that into excel, Now i have closed excel and i have few updated in the excel how to sync that to sharepoint. Kindly help (I need to sync the same excel with few updated to same list in sharephone)

    ReplyDelete
    Replies
    1. Hi Harry,

      Thanks for your comments. This solution is quite old and not very well supported. When you close the file, your only option to sync is to re-sync to a new list.

      Delete
    2. Hi Dimitar,

      Could you please let me know if there is any other way to re-sync the list without creating a new list.

      Thanks in advance.

      Delete
    3. Hello, you need to keep the Excel file open and the macros loaded in order to do that. Once you close it, there's no resync.

      It's a legacy feature from 2007...

      Delete
  4. Hi Dimitar,

    Thank you for the quick reply, Just for your info, Earlier with MS Office 2010 with addin "SynchronizeWSSandExcel.xlam" our user were able to sync the edited excel successfully to same share point list, After we migrated to MS Office 2016 we are unable to perform this and lot of our users are used to this procedure. Kindly help me if any other procedure to fulfill this requirement. (we are facing problem after upgrading to MS Office 2016).

    Regards
    Harry

    ReplyDelete
    Replies
    1. Hi Harry, I've updated the article to mention that this does not work with 2016.

      Delete
    2. Hi Dimitar,
      Is there any work around to keep this working for 2016 as well? I am eagerly awaiting for your reply.

      Thanks in advance,
      Dinkar

      Delete
    3. Hi Dinkar,

      I am able to work with this on Excel 2016 and SharePoint Online. You still need to run the add-in first and then open your Excel file to sync.

      Delete
  5. I just wanted to add an additional source of Excel Add in for SharePoint that is 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
  6. Hi Dimitar, Hi have some formulas in my header of the spreadsheet. When i tried to export to shareoint i am loosing those formulas. Please help me

    ReplyDelete

  7. The great service in this blog and the nice technology is visible in this blog. I am really very happy for the nice approach is visible in this blog and thank you very much for using the nice technology in this blog
    MS office course in chennai

    ReplyDelete
  8. That is really nice to hear. thank you for the update and good luck. this website

    ReplyDelete
  9. If you are looking for more information about flat rate locksmith Las Vegas check that right away. budget tracking template

    ReplyDelete
  10. Controlling information is the place where the intensity of Excel lies. Excel spreadsheet consultant

    ReplyDelete
  11. They have built up an exceptional add-in using a re-sizeable drifting window and hyperlink framework to help Excel clients comprehend a recipe and proficiently leap to the entirety of its point of reference and ward cells and ranges regardless of whether they are on an alternate worksheet or an alternate open exercise manual. spreadsheet guru

    ReplyDelete
  12. Mike established The Smart Method in 2002 as IT preparing was the region he needed to get into. He has shown Excel courses for individuals from a portion of the world's biggest organizations in nations in Europe and America. placement cells database

    ReplyDelete