Using Excel (Excel 2003) Data Source to Retrieve Data from Sharepoint List

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

Note:

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.

Related posts:

1. Publish Excel 2007 data onto SharePoint