Import data from a SharePoint list to Excel

You can import and analyze data from a list in Microsoft Windows SharePoint Services 3.0 or a Windows SharePoint Services 2.0 Web site. For example, you can import a spare parts inventory list from a SharePoint site, and then create a PivotTable report in Microsoft Office Excel 2007: A chart that provides interactive data analysis, similar to a pivot table. You can change the view of the data, view the different levels of detail, or rearrange the layout of the chart by dragging the field and showing or hiding items in the field. To summarize and compare the data.

Overview

To import data from a SharePoint site, you must have Office Excel 2007 installed on the computer where you want to use the SharePoint list.

After you import data from a SharePoint site into Office Excel 2007, you can analyze the data in Office Excel 2007, but any changes you make in Excel are not reflected in the list on the SharePoint site.

To use Office programs to manipulate the data in a list on a SharePoint site and reflect those changes in a SharePoint site, you can use Microsoft Office Access 2007 or use the Visual Basic for appli Cations (VBA) solution.

In addition, you can manipulate the data in a SharePoint list in a workbook that is saved as an Excel 97-2003 workbook (. xls format). However, if you save an Excel 2003 workbook as an Office Excel 2007 workbook (. xlsx format), you can only provide a read-only connection from a SharePoint list.

When the list continues to be updated on the site, you can update the workbook with the latest data from the SharePoint site. When you update data from a SharePoint site, the original data from the SharePoint site and any changes you made in Office Excel 2007 will be overwritten.

In Office Excel 2007, you can import data into an existing worksheet, or you can create a new worksheet. If a list on a SharePoint site contains a folder, the structure does not appear in the resulting Excel worksheet.

When you import data from a SharePoint list that has a read-only connection into the list, the project type and path columns are added to the data on the worksheet. Use these two columns to filter and sort data based on the type of data and its location on the site (for example, the data is in a subfolder of the list).

To import data from a list on a SharePoint site, you must be connected to a SharePoint site and have permission to read the list.

Import data from a SharePoint list

You can import data from a list on Windows SharePoint Services 3.0 or a Windows SharePoint Services 2.0 Web site. When you import data from a SharePoint list, some of the steps will vary depending on the version that is running on the SharePoint site.

　　How to determine which version is running on a SharePoint site

• In the upper-right corner of the Windows SharePoint Services 3.0 Web site, you may see a menu that contains your user name or account.

• On Windows SharePoint Services 2.0 sites, the top-level navigation in the upper-left corner may contain home, document and list, and help links.

　　Note site owners or administrators can customize your site. If you are unsure which version to use, contact the site owner or administrator.

1, do one of the following on the SharePoint site:

Windows SharePoint Services 3.0

1, if the list is not already open, click its name on the Quick Launch. If the name of the list does not appear, click View All site content, and then click the name of the list.

2, on the Action menu , click Export to Spreadsheet.

3. If you are prompted to confirm the operation, click OK.

Windows SharePoint Services 2.0

1, if the list is not already open, click Documents and lists, and then click the name of the list.

2, on the page that displays the list, under Actions, click Export to Spreadsheet.

Second, in the File Download dialog box, click Open.

When you are prompted to enable a data connection on your computer, click Enable if you think that the connection to the data on the SharePoint site is safe to enable.

Four do one of the following:

• If you don't have a workbook open, Excel creates a new blank workbook and inserts the data as a table in the new worksheet.

• If the workbook is open, do the following in the Import Data dialog box that appears:

1. In the "Please select this data to display in the workbook", click Table, PivotTable, or PivotChart and PivotTable report.

2. Under where you put the data, click Existing sheet, new worksheet, or New workbook.

If you click Existing worksheet, click the cell in the upper-left corner of the list that you want to place.

3, click "OK".

Update data from a SharePoint list

When other users continue to update the list on the SharePoint site, you can update the data in the worksheet with the most recent changes. But doing so overwrites the data originally imported from the SharePoint list and changes to the data in Excel.

1. Click anywhere in the table you want to update.

2, on the "External Table Data" tab, click Refresh.

　　Tip If you no longer need a connection between the worksheet and the SharePoint site, you can disconnect the table. On the External Table Data tab, click Unlink.