Use the Connection Properties dialog box to control various settings for connections to external data sources, and to use, reuse, or switch connection files.

Security 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 , , and .

Connection Name and Description Displays the connection name and optional description. To change the name and description, click the text in the box, and then edit the text. The connection name and description are displayed in the Workbook Connections dialog box. (On the Data tab, in the Connections group, click Connections.)

Note The connection name and description are stored in a Microsoft Office Excel workbook only, and they cannot be saved to a connection file.

Usage

The settings on the Usage tab control the way that the connection information is used in the workbook.

Refresh control

Enable background refresh Select this check box to run the query in the background. Clear this check box to run the query while you wait. Running a query in the background enables you to use Excel while the query runs.

Refresh every n minutes Select this check box to enable automatic external data refresh at regular time intervals, and then enter the number of minutes between each refresh operation. Clear this check box to disable automatic external data refresh.

Refresh data when opening the file Select this check box to automatically refresh external data when you open the workbook. Clear this check box to immediately open the workbook without refreshing external data.

Remove data from the external data range before saving the workbook Select this check box if you want to save the workbook with the query definition but without the external data. Clear this check box to save the query definition and data with the workbook. This check box becomes available only after you select the Refresh data when opening the file check box.

OLAP Server Formatting

Controls whether the following OLAP server formats are retrieved from the server and are displayed with the data.

Number Format Select or clear this check box to enable or disable number formatting, such as currency, date, or time.

Font Style Select or clear this check box to enable or disable font styles, such as bold, italic, underline, or strike-through.

Fill Color Select or clear this check box to enable or disable fill colors.

Text Color Select or clear this check box to enable or disable text colors.

OLAP Drill Through

Maximum number of records to retrieve Enter a number from 1 to 10,000 to specify the maximum number of records to retrieve when you expand a level of data in a hierarchy.

Language

Retrieve data and errors in the Office display language when available Select or clear this check box to enable or disable the retrieval of translated data and errors, if any, from the OLAP server.

Definition

The settings on the Definition tab control how the connection information is defined and the source of the connection information, either the workbook or a connection file.

Connection type Displays the type of connection that is used, such as Office Data Connection or Microsoft Office Access Database.

Connection file Displays the current connection file that is used to store the connection information and enables switching to a revised or new connection file. If this field is blank, a connection file was never used, or it was used and then modified so that the link to the connection file was broken.

To re-establish the link to the connection file, for example because it was updated and you want to use the new version, or to change the current connection and use a different connection file, click Browse, which displays the Select Data Source dialog box of the Data Connection Wizard. You can then select the revised connection file, a different connection file, or create a new connection file by clicking New Source.

Note Make sure that the new connection file is consistent with the object that has the data connection. You can change the connection, but you cannot switch connections between the following objects:

Always use connection file Select this check box to ensure that the most up-to-date version of the connection file is always used whenever the data is displayed or refreshed. Clear this check box to use the connection information in the Excel workbook.

Important If the connection file is not available, Excel resorts to the connection information that is saved in the workbook. If you want to ensure that the most up-to-date version of the connection file is always used, make sure that the connection file is accessible and available.

Connection string Displays the current connection information in the form of a connection string. Use a connection string to verify all of the connection information and to edit specific connection information that you cannot change through the Connection Properties dialog box.

Save password Select this check box to save the username and password in the connection file. The saved password is not encrypted. Clear this check box to log on to the data source, if a user name and password are required, the first time that you access it. This check box does not apply to data retrieved from a text file or a Web query.

Security Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

Command type Select one of the following command types:

Command text Displays the name of the command type. To change the command text, click the text in the box and then edit the text.

Excel Services: Authentication settings Click this button to display the Excel Services Authentication Settings dialog box and to choose a method of authentication when you access the data source that is connected to a workbook and that is displayed in Excel Services. Select one of the following options to log on to the data source:

Note The authentication setting is only used by Excel Services, and not by Microsoft Office Excel. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same.

Edit Query Click this button to change the query that is used to retrieve data from the data source. Depending on the type of data source, displays one of the following:

Note You cannot edit the query if the connection information is currently linked to a connection file.

Parameters Click this button to display the Parameters dialog box and to edit Parameter information for a Microsoft Query or Web Query connection.

Export Connection File Click this button to display the File Save dialog box and to save the current connection information to a connection file.

See also: