When I started working with SharePoint 2010 back in August 2009, I was thrilled when I found out that out of the box, I can synchronize a Project 2010 task list to a SharePoint 2010 project task list.
When I started working with SharePoint 2010 back in August 2009, I was thrilled when I found out that out of the box, I can synchronize a Project 2010 task list to a SharePoint 2010 project task list.
When I started working with SharePoint 2010 back in August 2009, I was thrilled when I found out that out of the box, I can synchronize a Project 2010 task list to a SharePoint 2010 project task list.
My world fell apart when I realized that the capability to synchronize Excel spreadsheets with a SharePoint list was no more in 2010. It bothered me why this was taken away since I can easily do this with Excel 2007 and SharePoint 2007.
While working on an expense report in Excel 2010 tonight, a crazy idea dawned upon me “Hmm, I wonder if I can install the Excel 2007 Add-in that allows me to sync an Excel table to a SharePoint list in Excel 2010 … ” Long story short, it worked!
I was overjoyed (do you hear Stevie Wonder in the background?) that tears started to fall … OK, OK, enough of the drama. Here are the steps on how you can synchronize an Excel 2010 spreadsheet to a SharePoint 2010 list:
1. Make sure you have Excel 2010 installed on your machine (just makin sure).
2. Download and install “Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists” to your copy of Excel 2010.
3. Create or open an Excel Spreadsheet (make sure the file is saved as .XLS)
4. Create an Excel table (click here to learn how)
5. In Excel 2010, from the Design tab, click Publish and allow Sync
6. Enter the SharePoint site URL, list name and description
7. Click Publish
That’s it! You can now update either the Excel spreadsheet or SharePoint list and it will be in sync. Make sure you manually sync Excel with SharePoint by right-clicking within the Excel table and select Table | Synchronize with SharePoint.
Lastly, if you are wondering, you can do this with SharePoint Foundation 2010. You won’t need Excel Services 2010 at all.
See video demo here: http://youtu.be/hvGZotB2SZs
Good stuff… but when i try to save the workbook I get the following error:
“The file format you are saving in does not suppport synchronizing the tables to the Sharepoint site by using Excel. “…if you save the file it will convert the tables to read-only data. Any advice?
HI, Gab – make sure you save it as .XLS
i did
Anyway to make this an automat update? Just one way from Excel to SharePoint, I need to query the contents from one DB and publish them all to SharePoint list, it’s not practical to manually update it every time.
Like you I cried, I was overjoyed, I sang Stevie Wonder. However when I ran it I entered the sharepoint address, the table name and hit Publish, but it throws a VB runtime error asking me to debug. Now I cry, and sing the Blues ;^)
UPDATE:Happy again. Turns out you have to be very careful and specific about the Sharepoint URL you use. I was truing to drop it in a sharepoint “List” folder (for example mysite/subfolder/list/) which it doesnt like. File seems like it needs to be placed in the root of site or subsite. And as an FYI, this sync functionality is also native to SP Enterprise I believe. Many thanks for this tip.
This is quite simply a joke. The option does not work no matter what I try. It’s amazing to me that MS would remove this functionality from Excel in the first place.
Let me guess, it was just too convenient and the bozos running the show felt there weren’t already enough reasons for us to be annoyed with them.
Put the freaking functionality back (PUBLISH FROM EXCEL TO A GD SP LIST!!!!).
….waiting for the next big thing…so I can dump MS in the trash…
I have to agree. It doesn’t work when you have an existing list in Sharepoint which you simply want to update. It throws and error saying it already exists. Duh!
Anyway, if anyone has resoved this in a manner that a typical user can manage, let me know. Otherwise, this is silly that MS doesn’t fix this right.
This is not working for me either…and yes I saved as XLS and created a table, was careful with sharepoint urls, etc – just get the VB runtime error.
For what it is worth I was using Excel 2010 RTM hitting WSS 3 / Sharepoint 2007.
Someone really need to do this right as a reliable plug-in that actually guides the user. Years ago I wrote a sharepoint list browser and datasource for Visual Studio. It is not that hard as sharepoint exposes all the features needed, it is more that someone needs to do this in a more robust way. Obviously both Sharepoint and Excel have all the ‘hooks’ needed. I would guess this current plug-in was a part time 1-man effort from a Microsoft dev.
Anyway..I am moving on, this was not a huge issue for me anyway.
Just like you I was looking for a way to make the sync work again in Sharepoint 2010 and Excel 2010 and I think I found it…
The piece of software has been developed by a company called SoftFluent and is available for download (for free for non commercial use) here :
http://www.softfluent.com/wsslists.htm
It works just fine.
If you are happy with the result please leave them a kind message, they are good friends 🙂
Instructions for working with an existing SharePoint List: http://support.microsoft.com/kb/930006
I’ve now successfully tested this with an integrated VBA macro to update report metadata in SharePoint 2010 and Excel 2010.
Please note: This method does not support adding rows to an existing list, only modifying an existing row in a SharePoint 2010 list.
I am modifying report metadata, so I: Save a new report to the document library first, refresh the metadata list, and then update fields on the new row.
I expect you could find a workaround to create a new row through another VBA process and then update that row through this.
Try this tool for working with existing ShrePoint lists
http://www.softfluent.com/xlsync.aspx
Daniel COHEN-ZARDI (SoftFluent)
Hi Dux,
Thanks a ton! so glad to see this post.. its perfect! I have tried doing the same and its working fine. But now, only one thing bothers me. I’m unable to view this share-point list in datasheet view! How do I make this happen? Please help me. {using excel 2010 & Sharepoint 2010}
Just go to All site content – Create – Import spreadsheet.
Make sure your site is in trusted sites.
Done
Thanks! Really cool!
Great tip… thank you. The feature in SharePoint 2007 saved me and others a lot of time…
Has anyone overcome the issue when trying to save the excel file after the following error appears: “the file format you are saving in does not support synchronizing or updating tables on the SharePoint site by using Excel?” (I am using Excel 2010)
Dubs, Thanks for that link. As for adding new rows, select a cell in the Excel table, then using the TablesDesign tab, select Resize Table and increase the row count. ex: =$A$1:$F$320 to add 5 more rows would now read =$A$1:$F$325
We had this working perfectly with Office 2010 and SharePoint 2007, but now SharePoint has been upgraded to 2010 it’s broken. I get a runtime error when I try to Publish and Sync and xls to SharePoint. In fact, a new list does appear in SharePoint despite the error, but all the data in the source file disappears. I’m so effing frustrated I could cry. I hate Microsoft.
I was able to get this working but only in one direction. It works fine if I update the list in sharepoint. WhenI try and update a cell in excel as soon as leave the cell it reverts back to the original entry. Almost like the excel file is protected. If unlink the table from the sharepoint site I can update the cells without an issue. What am I missing here? I am using sharepoint 2007 and excel 2007.
hi as i have go through with these steps all thing working fine but one thing which is going to trouble me each time it ask me a different list to update the items in excel workbook. please suggest .
Read closely, Megan…”Make sure you manually sync Excel with SharePoint by right-clicking within the Excel table and select Table | Synchronize with SharePoint.” Works great, thanks Dux!
Hi Dux, it is very helpful. I have followed every of your step and it seemed fine until I clicked the synchronize, it gave me the error window like “An unexpected error occred. Changes of the data cannot be saved” Do you know the reason? I save the 2010 file into 2003/2007(.XLS) and clicked the the Add-in for Sync in that 2003/2007 file. I closed the file and re-opened it again and it still didnt work. Our company calls the sharepoint website as Project Server. I dont know the version of it, it should be new. Do you think it is because of the version (like you said server 2010) I also did a little research saying if the error meesage showing that List Does Not Exist, meaning you have to use Export(one-way synch) to publish it first then use the Publish and Sync button to publish it again. But they all gave the same error messages. Thank you so much for reading my message. Hope to hear from soon.
Nice nice but I’d like to utilize excel web services to centralize the excel document. So I used powerpivot for sharepoint to create a data feed then in excel workbook with powerpivot add-in I connected my datafeed via powerpivot. Then uploaded my excel worksheet to sharepoint and use excel webservices to refresh data from the sharepoint list automatically
OK, so there are a few things missing.
1) You have to browse and install the Excel 2007 SP synch Add-in under the Excel 2010 Options > Add-Ins > Go… > Browse… menu system, which will add the Publish and Synch icon under the Design tab.
2) The Address field HAS to be the root site url; it already KNOWS it’s a list (if you think of it in a directory-type format, it helps – it’s putting it in the list folder so it doesn’t need to be specified).
3) The Name field is the List name you are creating i.e. you CANNOT override/add to/export into a list you have already created within SP.
4) To synch with SP, as it has been pointed out, any data changed within the Excel spreadsheet needs to be synched manually by right clicking > Table > Synchronize with SharePoint; I have tried synching from Excel > SP with success but I haven’t tried it the other way around yet.
I hope these clarifications help and thanks, Dux, for solving this issue with such a clever workaround… why MS builds OUT functionality, I have no idea… I feel sure it has nothing to do with third parties developing software that they likely generate additional revenue from… that would just make NO sense, right?
Thanks, Dux. Works perfect.
2 remarks (already mentioned in previous comments):
1) Only works from Excel to SP
Not the other way, unfortunately (please let me know if I’m wrong!)
2) Only in XLS format, not XSLX
Not a big deal…
Here is a workaround.
Do a linked list in Access from SP. Then in Access you can create a view from the linked SP list. Now such Access view can be easily linked to the excell through the standard excell 2010 functionality (Data -> From Access).
same as ernst I have tried it and works only from excel to SP. Anyone knows how to implement Aidas’s workaround? TIA
See this video demo:
http://youtu.be/hvGZotB2SZs
Hey Dux, this worked great for me but now in last couple of days am seeing a strange problem. I inset a new table row in excel to update values, but for each cell I enter a value in, the value in the id column increments by 1 ! Pls help advise.
I have used the Excel Add-In with both excel 2007 synced to Sharepoint 2007 and 2010….and it works great. However, when moving to excel 2010 with Sharepoint 2010, has anyone noticed that if you get beyond 20 rows in your spreadsheet and try to sync something weird happens to the 20th row. By weird I mean….set up the table with 25 rows, then sync to new list in Sharepoint 2010. Then change something on the 20th row and re-sync. For some reason the ID of the row is incremented and a new row is added to the sharepoint list. This glitch only seems to occur on the 20th row. Now, this may be me, but I have tried this on various PCs, uninstalled, re-installed. Always the same.
Can anyone reproduce or shed any light ??
Andy
Why all the add-ins and workarounds?
You simply create the workbook in Excel 2003. Export from SP10 in to Excel 2003 and leave it as .XLS
As long as you have all required fields the view will retain two-way sync functionality.
Negative of this is people who open in Excel 2010 can only update list items, people who open it in 2003 Excel will still have the * row to add new items.
I am having the same issue as AMU1962. Going from Excel 2010 into SP 2007. Syncing is causing insertion of blank rows.
So, using the Add-in requires typing in the URL and the SP List name all the time. Ditto for the existing Tabled>Tools>Export command in XL 2010. And you cannot use either to replace data in an existing SP List. 🙁
So, using the Add-in requires typing in the URL and the SP List name all the time. Ditto for the existing Tabled>Tools>Export command in XL 2010. And you cannot use either to replace data in an existing SP List. 🙁
FYI.. Just tried with Excel 2013/SP 2013 and it still works!
FYI… Just tried with Excel 2013/SP 2013 and it still works just fine!