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