Configuring Excel Services and Data Connections

When you surface an excel spreadsheet using excel services and the excel web access webpart there are several places that you need to configure to ensure that excel files with data connections work properly.  In this scenario, we have an excel spreadsheet with an embedded data connection that we needed to surface in a MOSS 2007 intranet environment.  At a high level the components that need to be configured are as follows:

1. the Excel spreadsheet's data connection properties
2. Shared Service Provider (SSP) Excel Services Settings
3. SSP Trusted file location properties

Excel spreadsheet

First go into to the Excel spreadsheet and configure the data connection properties.  In this case we're using an embedded connection - in another post we'll describe how to configure a data connection file.

Select connections, then in the resulting dialog box highlight the data connection and click properties.  You should now see the following:

In the usage tab, you can set a couple of different refresh options.  In our case, we wanted to have the data refreshed when the file is opened.

Switching to the Definition tab and clicking on the authentication settings should give you the following dialog box:

For our case we wanted to use the credentials embedded in the spreadsheet's data connection so we selected the "None" option.  Click OK and close all of the dialog boxes.  The excel spreadsheet configuration is complete (assuming your data connection string is properly configured).

SSP Excel Services Settings

Now open your SSP and navigate to the Edit Excel Services Settings under the Excel Services Settings header in the right column.  In the last section titled "External Data" make sure the Unattended Service Account is completed with a domain level service account.  If you do not have a windows domain account entered here you will likely get errors when you try to refresh the data connection.

SSP Trusted file location properties

Lastly, go into "Trusted File Locations" under the Excel Services Settings header in your SSP and configure them.  There are three particular sections we'll be concerned with right now:

• Location
• Calculation behavior
• External Data

Within the location section simply add the URL of the document library you want to serve your Excel spreadsheets from.  Alternately you can trust the child sites/libraries if this is appropriate.

Next scroll down to calculation behavior and modify the cache lifetime and calculation mode.  In our case, we set the mode to automatic.

Lastly, under External Data make sure the following settings are configured properly:

• Allow external data: Trusted data connections and embedded
• Warn on Refresh: make sure this is unchecked or you'll get a fairly unfriendly warning screen before users can access the spreadsheet.  Many of our users thought this indicated something was broken

.

One last point to make about trusted file locations has to due with the Trust children option.  Be careful when you use this setting and the have other folks add other trusted file locations that are under the parent location you set up.  In this scenario it is very easy to get things out-of-sync or at least see behavior that you wouldn't expect given your original settings.

That should get you started using Excel Services and embedded data connections.

-Chris