Using Excel 2003, one can create a data source to retrieve data from a sharepoint list. This method has also the added benefit of displaying columns that are not generally displayed in a traditional export to excel. For example, in the Bug Database, 'Assigned To' and 'Owned By' columns are not displayed when the list is exported to excel using Excel 2003 (It is displayed with Excel 2007 export, though!).

• Open Microsoft Excel 2003
• Select Data > XML > XML Source from the menu. The 'XML Source' window is displayed to the right
• Click 'XML Maps'
• Click 'Add' in the XML Maps window
• Select 'New Source' in the 'Select XML Source' window. This displays the 'Data Connection Wizard' window
• Select 'Microsoft SharePoint Services lists' from the list and click the 'Next' button
• Enter the location of the data that is to be retrieved and click 'Next'
• Select the object or list that contains the data you need. Click 'Next'. 'Select Fields' window is displayed
• Select the columns required from the 'Available Columns' region and click 'Add'. Click 'Next'
• Specify 'Sort Criteria' and click 'Next'
• Specify 'Filter Criteria' to filter the displayed data and click 'Next'
• Specify whether you would like the number of rows to be retrieved and click 'Next'
• Enter a 'File Name' and 'Description' and click the 'Finish' button to complete.
• Note that the file name is displayed in the 'Select XML Source' window. Click 'Open'
• Click 'OK' in the 'XML Maps' window. Note that all the selected rows are displayed in the 'XML Source' region on the right side
• Map the elements by dragging the required rows to the Worksheet. What we have is the report with the header rows displayed; time to get the data
• Select Data > XML > Refresh XML Data from the menu to import the data
• Format the columns as required. Formatting may be required to align the cell display. Macros can be put to good use here

TrackBack URI for this entry

Comments (5)

Subscribe to this comment's feed

...
written by Raju Singh , November 15, 2009

Hiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii................

This tutorial is
very help full .

thank you

report abuse

vote down

vote up

Votes: +0

...
written by ibrahim , June 18, 2009

Hi

Try adding the following to the column in SP Designer i.e. open the column that has the html tags displayed in SP Designer and add the following:

disable-output-escaping="yes" Refer to this MSDN article for details

http://blogs.msdn.com/sharepointdesigner/archive/2008/09/20/using-disable-output-escaping-in-data-view.aspx

report abuse

vote down

vote up

Votes: -1

...
written by sc , June 10, 2009

In the 'Note' it is mentioned that "If the original list takes data in rich text format, you would notice that the imported data is displayed with the html tags and is not pretty to read. You may want to change the data input of a column to text (in the Sharepoint List) to resolve these"

I changed it and still some columns have html tags. It does not help changing the text format from rich text to plain text.

report abuse

vote down

vote up

Votes: +0

...
written by tsc , September 19, 2008

You need Excel Professional Edition for this. Try downloading xml toolkit from Microsoft and see if that helps. Not sure though if the toolkit will work with Standard Edition

report abuse

vote down

vote up

Votes: +0

...
written by ly , September 18, 2008

Hello

The XML menu is missing in my excel . .

report abuse

vote down

vote up

Votes: -1

Write comment

Name

Email

Website

Comment

smaller | bigger

Subscribe via email (Registered users only)

Write the displayed characters

Add Comment