Create, edit, and manage connections to external data

You can use Microsoft Office Excel to create and edit connections to external data sources that are stored in a workbook or in a connection file. By using the Workbook Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them.

Security Note: Connections to external data may be currently disabled on your computer. To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see Add, remove, or modify a trusted location for your files, Add, remove, or view a trusted publisher, and View my options and settings in the Trust Center.

Learn about data connections

Data in an Excel 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. The external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in, query, and access the external data source.

When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

Connection information can either be stored in the workbook or in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Universal Data Connection (UDC) file (.udcx). Connection files are particularly useful for sharing connections on a consistent basis and for facilitating data source administration.

If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. When 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, and not the original data connection file that may have been used to create the connection, indicated by the file name that is displayed in the Connection File property. Once you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.

Manage connections by using the Workbook Connections dialog box

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

• Create, edit, refresh, and delete connections that are in use in the workbook.

• Verify where external data is coming from, because, for example, the connection was defined by another user.

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

• Diagnose an error message about connections to external data.

• Redirect a connection to a different server or data source, or replace the connection file for an existing connection.

• Display the Existing Connections dialog box to create new connections. For more information, see Connect to (Import) external data.

• Display the Connection Properties dialog box to modify data connection properties, edit queries, and change parameters. For more information, see Connection properties.

• Make it easy to create and share connection files with users.

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

Identify a connection

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

Column	Comment
Name	The name of the connection, defined in the Connection Properties dialog box.
Description	An optional description of the connection, defined in the Connection Properties dialog box.
Last refreshed	The date and time that the connection was last successfully refreshed. If blank, then the connection has never been refreshed.

Add a connection

• Click Add to display the Existing Connections dialog box. For more information, see Connect to (Import) external data.

Display connection information

• Select a connection, and then click Properties to display the Connection Properties dialog box. For more information, see Connection properties.

Refresh the external data

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

  • To refresh specific connections, 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 a refresh operation, select one or more connections, and then click Refresh Status.

  • To stop the current refresh operation, click Cancel Refresh.

For more information, see Refresh connected (imported) data.

Remove one or more connections

• Select one or more connections to be removed from the workbook, and then click Remove.

  Notes: 

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

  • Removing a connection only removes the connection and does not remove any object or data from the workbook.

Important: Removing a connection breaks the connection to the data source and may cause unintended consequences, such as different formula results and possible problems with other Excel features.

Display the locations of one or more connections in the workbook

• Select one or more connections, and then under Locations where connections are used in this workbook, click the link Click here to see where the selected connections are used.

  The following information is displayed.

Column	Comment
Sheet	The worksheet where the connection is used.
Name	The Excel query name.
Location	The reference to a cell, range, or object.
Value	The value of a cell, or blank for a range of cells.
Formula	The formula of a cell, or for a range of cells.

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

Create an Office Data Connection (ODC) file

By using the Connection Properties dialog box or the Data Connection Wizard, you can use Excel to create an Office Data Connection (ODC) file (.odc).

1. Do one of the following:

   • Create a new connection to the data source. For more information, see the following help topics:

     • Exchange (copy, import, export) data between Excel and Access

     • Import or export text files

     • Connect to SQL Server Analysis Services Database (Import)

   • Use an existing connection. For more information, see Connect to (Import) external data.

2. Save the connection information to a connection file by clicking Export Connection File on the Definition tab of the Connection Properties dialog box to display the File Save dialog box, and then save the current connection information to an ODC file. For more information, see Connection properties.

Top of Page