Requirement: End-User has an Excel file, wants to create a SharePoint list with data from Excel file, and Synchronize between SharePoint Lists and Microsoft Excel. Fairly Simple, The very first option that comes to our mind is: Import Spreadsheet, isn’t it? nope, That can be used to Create List and import data but not when it comes to sync. Here the requirement is to synchronize the SharePoint list excel 2007.
Solution: Use the “Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists” to synchronize the SharePoint list with excel.
1. Download and install the Add-on:
This will create SynchronizeWSSandExcel.xlam under: C:\2007 Office System Developer Resources\Code Samples\XL2007SynchronizeWSSandExcel
2. Open Excel, Go to File >> Options >> Add-Ins, and click on the Go button next to “Manage: Excel Add-ins Dropdown.
3. Click on Browse Button and locate the SynchronizeWSSandExcel.xlam file, on the above location.
4. Now in Excel, Convert the data to the table (If it’s not table already) by selecting the data, click on Insert Tab, and choose “Table”.
5. Now, Place the cursor on your table. In design tab will see “Publish and allow Sync” button and fill SharePoint site details as below:
6. That’s all! You can now update data either from Excel or from a SharePoint list. Once you update data, You have to manually sync by right-clicking within the Excel table and select Table >> Synchronize with SharePoint. This will synchronize the SharePoint list and excel data.
I verified that it works with MS Excel 2010 and SharePoint Server 2010 as well.
Existing SharePoint list cannot be used! Doesn’t work when you close and Re-open. Oops!!
The above method works only when you create List from scratch and update/sync it further. If you’ve a List already in SharePoint and want to Import to Excel, to do some update, This method will not help. Also, This will work until you close the excel. If you re-open, it won’t work!!!
Want to keep One way sync from SharePoint List to Excel Sheet? Yes! its possible even when you close and reopen. How? Just export the List “Export to Spreadsheet” option from SharePoint list. Then from Excel, click on “Refresh” under data tab to get the latest data from SharePoint list.