Leading the way in Microsoft Office Development

Excel

You can export the contents of SharePoint lists, results of a survey or a document library to an Excel 2007 spreadsheet.

The exported list or library is a web query that stays updated with changed to the original list on the SharePoint site.

The Excel 2007 spreadsheet maintains a connection to the SharePoint list and therefore becomes a linked object.

(Actions > Export to Spreadsheet)

You can also initiate it from the datasheet which contains 4 options:

Query list with Excel

Print with Excel

Chart with Excel

Create Excel PivotTable Report

The export process exports only the columns and rows contained in the list's current view which is the All Items view by default ??

If none of the views contain the data you want to export then you must create a new view to meet your requirements.

Alternatively you can use an existing view and then delete the unwanted columns from Excel.

SharePoint will generate an Excel 2007 web query file (.iqy)

Excel 2007 opens a new workbopok that contains one worksheet named owssvr(1)

A Microsoft Office Excel Security Notice dialog box is displayed warning you that data connections have been blocked.

The Excel 2007 query results are displayed in the worksheet in an Excel 2007 table.

Each column in the list contains an AutoFilter arrow in the header row and the Design contextual tab is active

Excel named your table Table_Owssrv_1 ??

When you export a SharePoint library, Excel 2007 represents the documents in the list with hyperlinks that point to the documents on the SharePoint site.

Similarly attachments on list items are replaced with a hyperlink.

In the Excel 2007 spreadsheet click the link to open the file

You should make a habit of renaming your tables so that you recognize the data they contain.

This process helps formulas that summarize table data much easier to understand

To rename your table ensure the Design contextual tab is active and then in Properties group, edit the value in the Table Name field.

Calculated Columns

Any calculated columns in SharePoint will not automatically update when exported into Excel.

Although the columns in Excel 2007 retain the datatypes from the exported SharePoint list they do not retain the formulas.

One Way Synchronisation

The spreadsheet is updated with a copy of the data from the list

Any changes to the data in Excel 2007 are lost when you refresh the data.

This behaviour is different to Excel 2003 which does allow two-way synchronisation.