Edit a connection to manage an Excel workbook to external data

You can use Excel to create and edit connections to external data sources, which are stored in workbooks or connection files. You can easily manage these connections by using The Workbook Connection dialog box, including creating, editing, and deleting them.

　　Understanding Data Connections

The data in the Excel2007 workbook can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source such as a text file, a database, or an online analytical processing (OLAP) cube. An external data source connects to a workbook through a data connection, which is a set of information that describes how to find, log on, query, and access external data sources.

When you connect to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time you refresh the data, you will see the latest version of the data, including any changes to the data since the last refresh.

Connection information can be stored in a workbook or connection file (for example, an Office data connection (ODC) file (. odc) or a universal data connection (UDC) file (. udcx)). Connection files are especially useful for sharing connections in a consistent way and for facilitating data source management.

If you connect to a data source by using a connection file, Excel copies the connection information from the connection file to the Excel workbook. If you make changes by using the Connection Properties dialog box, you are editing the data connection information that is stored in the current Excel workbook, not the original data connection file that might be used to create the connection, which is identified by the file name that is displayed in the connection file attribute. Once you edit the connection information (in addition to the connection name and connection Description properties), the link to the connection file is deleted and theconnection file property is cleared.

　　Manage connections by using the Workbook Connection dialog box

　　The Workbook Connection dialog box helps you manage one or more connections to external data sources in the workbook. You can use this dialog box to perform the following actions:

• Create, edit, refresh, and delete connections used in workbooks.

• Verify the source of external data for several reasons, for example, the connection may be defined by another user.

• Show where each connection is used in the current workbook.

• Diagnose error messages about connections to external data.

• Redirect connections to other servers or data sources, or replace connection files for existing connections.

• Displays the existing Connection dialog box to create a new connection.

• Display The Connection Properties dialog box to modify data connection properties, edit queries, and change parameters.

• makes it easy to create connection files and share the file with users.

To manage connections in the current workbook, do one or more of the following:

　　Identifying connections

At the top of the dialog box, all the connections in the workbook are automatically displayed with the following information:

column	Notes
Name	The name of the connection, defined in the Connection Properties dialog box.
Description	Optional description of the connection, defined in the Connection Properties dialog box.
Last Refresh Time	Date and time when the connection was last successfully refreshed. If empty, the connection has never been refreshed.

　　Add a connection

• Click Add to display The existing Connection dialog box.

　　Show connection Information

• Select a connection, and then click Properties to display The Connection Properties dialog box.

　　Refreshing external Data

• Click the arrow next to refresh , and then do one of the following:

• To refresh a specific connection, select one or more connections, and then click Refresh.

To refresh all connections in the workbook, clear all connections, and then click Refresh All.

To get status information about the refresh operation, select one or more connections, and then click Refresh Status.

To stop the current refresh operation, click Cancel Refresh.

　　Delete one or more connections

• Select one or more connections that you want to remove from the workbook, and then click Delete.

　　Comments

• This button is disabled if the workbook is protected or if an object that uses the connection is protected, such as a PivotTable report.

Deleting a connection deletes only the connection and does not delete any objects or data in the workbook.

　　important Deleting a connection breaks the connection to the data source, and can cause unexpected results, such as different formula results and problems that may arise from other Excel features.

　　Displays the location of one or more connections in a workbook

• Select one or more connections, and then click link in this workbook where you want to use a connection click to see where the selected connectionwill be used.

The following information is displayed.

column	Notes
Work table	Use a linked worksheet.
Name	Excel query name.
Position	A reference to a cell, range, or object.
Value	The value of the cell, which is empty for the range of cells.
Formula	The formula for the cell or range of cells.

Selecting a different connection at the top of the dialog box clears the display of the current information.