Review: SharePoint 2010 Sync with Excel 2010

• Not SharePoint Enterprise
• SharePoint 2010
• Excel 2010 (although should be possible with 2003-2007)

Work is frustrating when I have to research on solutions that take more time than it would take for me to just create a custom application. The latest project is to create metrics from lists that were created in SharePoint.

At first glance, seems like a very simple task. Get access to the list, then aggregate the data. But of course, Microsoft does not make this easy at all. First you need to have so many other MS products which I (unfortunately) do not have access to.

Cons

• Only Excel to SharePoint
• I could not find a way to have sync if the list originated from SharePoint first
• Must be saved as 2003 (*.xls) to save the connection for syncing
• If you lose the connection, by saving as 2010, deleting the excel file, or losing the file, you lose the connection
• I was unable to find a way to reconnect for syncing
• BUT- I was able to refresh data in excel to data from SharePoint with Refresh data
• Does not save with Excel 2010
• It will work when you export to SharePoint but cannot save the connection
• When saving select *.xls (2003)
• Who knows how long this will be supported for as his has already been deprecated in 2010
• Clearly MS trying to require more customers to purchase more products like Access when most of companies already have MS SQL

Although the ability to update SharePoint lists from Office Excel 2007 is deprecated in favor of publishing and synchronizing lists using Office Access 2007, you can use the Excel 2007 SharePoint List Synchronizing Add-in to update SharePoint lists from Office Excel 2007. ~ (Reference-1 MSDN)

Pros

• More feed for Microsoft haters
• At least the refresh is sufficient for reporting purposes since SharePoint is the primary entry of data
• It appears there are other solutions but have financial costs
• Free if you already have SharePoint (non-enterprise version) and Excel 2010

Reference

http://itblog.wolthaus.net/2012/03/synchronize-excel-2010-with-sharepoint-2010-list/ - Synchronize Excel 2010 with SharePoint 2010 List

• Does not address having SharePoint list before having the excel file
• Does not address if you somehow lose the excel file or create a separate file

https://www.youtube.com/watch?v=vay-J97xl44 - Export Excel Lists To SharePoint And Connect Them

• This is most helpful for connection only
• Does not include setup, see link above for SynchronizeWSSandExcel.xlam

https://www.youtube.com/watch?v=OZdZMaKOg7s - QA0001 - Synchronise SharePoint with Excel

• Video I found for SharePoint 2013 (enterprise) and Excel 2013 (untested since I do not have either versions)

https://www.youtube.com/watch?v=EuW8QwAPSBw


Other SharePoint 2010 Tips
http://www.mssharepointtips.com/tip.asp?id=956&page=2


1 - http://msdn.microsoft.com/en-us/library/bb462636%28office.11%29.aspx#Office2007SynchronizeSharePointListfromExcel_Synchronizing