Export List Data To Excel and keep it in sync with List changes

There is built-in functionality in SharePoint that allows you to export your Site List data into an Excel Spreadsheet.  There is also a built-in Data Connection and Refresh option in Excel that allows you to keep your offline spreadsheet in sync with the Site List changes.

Please note this is a one way synchronization only from Site List data to Excel. Any changes that are made to your Excel file are overwritten the next time you select to Refresh your data.

Many use this feature in three main ways;

1. To view their List data while offline.

2. To produce Excel Charting or PivotChart reports in Excel.

3. To use Excel Services to display charts and graphs that are actually created in the Excel Spreadsheet they are exporting data to. In this way you would upload your Excel Spreadsheet to your Reports Library and Convert it. So both the List data and Excel spreadsheet are stored in your SharePoint site, but users are not accessing or updating that Excel spreadsheet. It is only being used as the graphing mechanism.

Instructions how:

1. Go to the SharePoint Site Directory.

2. Select your Site.

3. Go to the List you want to export data from.

4. Go to the Actions toolbar

5. Select Actions - Export to Spreadsheet

6. Select Open

7. In Excel Save As Office 2003 format (required)

8. It will create two extra columns; Item Type and Path, this is for sorting purposes primarily.

When you want to update the Excel spreadsheet with the Site's List data again:

1. Open the Excel Spreadsheet.

2. Click in the table area of the data.

3. Go to the Design tab.

4. In the External Table Data group click Refresh.

For small lists this will update very fast and you may not even notice it. Remember, when you click Refresh it will replace all Excel table contents with your current Site List data. And this is not a two way synchronization solution. Try Microsoft Access or VBA if you need some form of two way synchronization.

Technorati Tags: SharePoint export list data to excel,Excel data connection file

del.icio.us Tags: SharePoint export list data to excel,Excel data connection file