Friday, March 20, 2009

Using Microsoft Excel with SharePoint 2007

Nice article and tip for end users which is often overlooked is the ability to export a SharePoint list into Excel. Here's a nice write-up on how to do this. Using Microsoft Excel with SharePoint 2007

I would like to point out one major error in the article: "You can't use Excel to edit the data that's stored in SharePoint. That's because the SharePoint data is linked to Excel through a Web query file. In this particular scenario, a Web query file facilitates only one-way operations."

This isn't true at all. The authors of the article fail to mention the List Toolbar which does offer the ability to Synchronize your SharePoint list within Excel. You have to have edit rights to the list in order to write back to the list.

In Excel 2007
You can add the 'Synchronize List' option to your Quick Access Toolbar by clicking the Office Button -> Excel Options (bottom right) ->Customize -> Change the first dropdown to Choose Commands from All Commands and locate the Synchronize List option. Click Add. Now the shortcut will appear at the top of Excel next to your save, undo, and redo changes.

Note you can also click the small arrow next to the Quick Access Toolbar and select More Commands.

Don't you love how intuitive Excel 2007 is! (note the sarcasm)

In Excel 2003
Just enable the List Toolbar by View->Toolbars - List

One key point the article does make is the inability to apply formatting. In order to do that, you'll need to create separate sheets in your Excel workbook and use lookup formulas to the data.

3 comments:

  1. How can we make use of this option? I have added this new command, but it is always grayed out... I tried exporting lists from SP, I have also tried importing spreadsheet into SP.. but either way the option is not active.. BTW I have also tried slecting some cells and Table to check if the options works..but in vain.. can you elaborate on this please...

    ReplyDelete
  2. Vamshideep, which option is grayed out? Do you have write access to the list? Is approval turned on?

    ReplyDelete
  3. The option to synchronize list is grayed out...I have farm administration access and full access of the list... there is no content approval on the list... nor any workflow is acting on the list... Well I am using Excel 2007..but still It is not working.... I left fighting on it... do let me know if you have any other thoughts... thanks again.. Do you think it does synchronize properly with SharePoint list using this option?

    ReplyDelete