Connection properties

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

 Important   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 Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center

Connection name and Description boxes
These display the connection name and an 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 as columns in the Workbook Connections dialog box. (On the Data tab, in the Connections group, click Connections.)

In this article


Usage tab options

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.

Top of Page Top of Page

Definition tab options

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:

  • An OLAP PivotTable or PivotChart report
  • A non-OLAP PivotTable or PivotChart report
  • An Excel table
  • A text file
  • An XML table
  • A Web query to a Web page

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 (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) 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:

  • SQL
  • Table
  • Default

If the connection is to an OLAP data source, Cube is displayed, but you cannot change it.

Command text     Specifies the data returned based on the command type. For example, if the command type is Table, the table name is displayed. If the command type is SQL, the SQL query used to specify the data returned is displayed. To change the command text, click the text in the box and then edit the text.

Excel Services    Click the Authentication Settings 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:

  • Windows Authentication    Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
  • SSO    Select this option to use Single Sign On, and then enter the appropriate identification string in the SSO ID text box. A site administrator can configure a SharePoint site to use a Single Sign On database where a user user name and password can be stored. This method can be the most efficient when there are many users.
  • None    Select this option to use information saved in the connection string for authentication, for example, when you select the Save Password check box.

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.

 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:

  • The Data Connection Wizard for an Office Data Connection (ODC) file (.odc) OLEDB connection.
  • Microsoft Query for an ODBC connection.
  • The Edit Web Query dialog box for a Web page.
  • The Text Import Wizard for a text file.

 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.

Top of Page Top of Page

 
 
Applies to:
Excel 2007