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

Using Excel 2003, you can create a data source to retrieve data from a SharePoint list. This method also has the added benefit of displaying columns that are not usually displayed in a traditional export to Excel. For example, in the fault database, 'Assigned to' and 'ownership' columns are not shown when the list is exported to Excel using Excel 2003.
* 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 for data collection in rich text format, they would realize that the imported data is displayed with the html tags and not pleasant to read. You may want to change the input data in a column of text (in the SharePoint list) to resolve these.