Microsoft Office Tutorials and References
In Depth Information
Connecting to Data
Depending on the permissions and other settings on the DCL, an approval workflow might be enabled
on the DCL. In that case, new files will be usable only when approved by the authorized users.
There are multiple types of data connection files. Excel and Excel Services use Office Data Connection
(*.odc) files. Other applications can use other file types with the DCL. For example, InfoPath uses
Universal Data Connection (*.udc) files.
Using a Connection from Excel
One of the difficulties for end users when creating a new PivotTable or other external data object is to
find the correct connection. Creating a new connection from scratch is a major obstacle for many users.
To work around this, you can browse to an existing connection in a DCL. From the Connections dialog
box, click the Browse For More button, and browse to the DCL. Select the relevant data connection file
and click Open.
Data Connections in Excel Services
Excel Services can use data connections from DCLs in order to benefit from central management and
security of data connections. In addition, Excel Services can use data connections that are embedded in
Trusted Data Connection Libraries
Excel Services manages a list of trusted DCLs and allows users to open connection files from these
In a SharePoint farm, there might be many DCLs. To increase the server security, you should define
only the DCLs that a small group of trusted individuals have permissions to manage.
Choosing the Connection
Workbooks can contain two variants for each connection: the embedded connection that is saved in the
workbook file and the path to a linked connection file. Excel Services determines which connection to
use based on two settings: the Always Use Connection File flag in the workbook and the Allow External
Data setting of the trusted location of Excel Services.
To set or clear the Always Use Connection File flag, open the Data Ribbon of Excel 2007, click Connections,
select the connection, and click the Properties button to open the Connection Properties dialog box. The
flag is in the Definition tab.
You can configure the Allow External Data setting to one of the following values:
None — No data connections are allowed from workbooks in that trusted location. All data
operations (such as refreshing a PivotTable or opening a workbook with refresh on open or periodic
refresh) will fail and return an error to the user. Use this value if, for security or performance
reasons, you do not want to allow workbooks from certain locations to access external data.