OfficeTalk: Let's Get Connected in Excel 2007

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Microsoft Office Excel 2007 greatly improves the ability to create and manage external connections to data sources. This column discusses how centralizing data source connections gives you much more control over the data you use in your Excel 2007 workbooks. (5 printed pages)

Frank Rice, Microsoft Corporation

July 2007

Applies to: Microsoft Office Excel 2007

Contents

  • Overview

  • Storing Connection Information in a Workbook or Separate File

  • Walkthrough: Creating Connections to External Data Sources

  • Centralized Connection Information in Excel 2007

  • Conclusion

  • Additional Resources

Overview

In this column, I explain a set of new features in the area of data connection management in Microsoft Office Excel 2007. Specifically, I describe three new things that you can do in a workbook that is connected to external data, whether that data is from a Microsoft Office Access database, an Oracle database, an Analysis Services cube, or some other data source. In an Excel 2007 workbook using the new user interface, you can:

  • Create connections to external data sources from the Office Fluent Ribbon.

  • See a list of the available data connections.

  • See where those connections are used.

  • Change the properties of a connection.

Since the term connection has different meanings for different people (especially developers), let me start by defining what it means in relation to Excel:

A connection is the information needed to identify an external data source, and to connect to that external data source, so that you can import some or all of the data into Excel 2007.

Put another way, a connection is nothing more than various pieces of information: the type of connection (such as Microsoft SQL Server, ODBC), the server name, the table name, optional parameters (such as Persist Security Info = True). Excel uses this information to fetch data such as a company's sales data or inventory data.

Storing Connection Information in a Workbook or Separate File

In this context, connection information is either stored inside an Excel workbook in what is call a workbook connection, or stored in a separate file, which is referred to as a connectionfile. Connection files created in the Microsoft Office system are given a file extension of .odc, which stands for Office Data Connection. Excel can open .odc files and establish connections based on their contents.

When Excel 2007 uses a connection file, it copies all of the connection information from the file into the Excel 2007 workbook. Excel then retains its own copy of the connection information, which it can use to fetch data from the external data source.

When you use the Excel tools to connect to an external data source, Excel stores the connection information in a workbook connection. It creates an .odc file that it saves in the My Data Sources folder. The purpose here is that next time you want to connect to the same data source, Excel uses the information in the file without the need to re-type all the connection information.

Note that nothing in this process is new to Excel 2007–it exists in current versions of Excel.

To summarize, we define the following items:

  • External Data Source. A database or data file.

  • Connection Information. The information needed to connect to external data.

  • Connection File. Standalone file containing the connection information.

  • Workbook Connection. Copy of connection information within an Excel workbook.

Walkthrough: Creating Connections to External Data Sources

Now comes the part of the process that is new in Excel 2007. In Excel 2007, a new dialog displays all the workbook connections that exist within the current workbook. You display this dialog box from the Data tab by clicking the Connections button.

Figure 1. The Connections button on the Data tab

Figure 2 shows the dialog box that is displayed when you click the Connections button and there are no connections in the current workbook.

Figure 2. The Workbook Connections dialog box

When you click the Add button in the Workbook Connections dialog box, the Existing Connections dialog box (see Figure 3) is displayed showing a list existing connections. There is also a Browse for More button where you can search for other connections.

Figure 3. The Existing Connections dialog box

When you select the external data source (in this example, a Microsoft Office Access database), Excel may display the Select Table dialog box depending on the type of data source selected, as seen in Figure 4.

Figure 4. The Select Table dialog box

After searching and selecting the data source, Excel adds the entry to the Workbook Connections dialog box as seen in Figure 5.

Figure 5. The Workbook Connections dialog box with data source connections

After adding the data source to the list, Excel creates a .odc file in the My Data Sources folder containing the connection information.

Figure 6. An ODC file is added to the My Data Sources folder

Note

Each connection in the workbook must have a unique name within the workbook.

You can see where a connection is used in the workbook by selecting it and then clicking the link in the lower half of the Workbook Connections dialog box.

Figure 7. You can see where the connection is used in the workbook

You can also get specific connection information by selecting the connection in the upper part of the Workbook Connections dialog and then clicking the Properties button. This displays the Connection Properties dialog box. This dialog box has two tabs: the Usage tab has settings for connection usage within this workbook.

Figure 8. The Usage tab in the Connection Properties dialog box

The Definition tab has settings that define the connection information.

Figure 9. The Definition tab in the Connection Properties dialog box

The button labeled Export Connection File on the Definition tab saves the connection information to an external connection file with a file extension of .odc.

After you define a data connection, you specify how you want to view the data and where you want it displayed by clicking Existing Connections button in the Get External Data group.

Figure 10. The Existing Connections button

This displays the Existing Connections dialog box.

Figure 11. The Existing Connections dialog box

Highlighting the connection and then clicking the Open button opens the Import Data dialog box.

Figure 12. The Import Data dialog box

In this dialog box, you specify how you want the display data such as a table or Microsoft PivotTable dynamic views, and where you want the data inserted in the workbook, either into the current worksheet or to a new worksheet. Clicking the OK button inserts the data such as to a table show in Figure 13.

Figure 13. In this instance the data is inserted into a table

Centralized Connection Information in Excel 2007

The ability to view all of the workbook connections from a central location and then to manage those connections with a standardized dialog is new in Excel 2007. In previous versions of Excel, connection information was stored with the object that used the connection. For example, the connection used by a query table was stored with that query table and the connection used by a Microsoft PivotTable was stored with that PivotTable. To browse all the connections in a workbook, you had to navigate, with code, to each of the objects in the workbook that might be bound to external data and then look at the object model for each of those objects to see the connection information.

By providing a centralized place for browsing and managing connectivity to external data, it becomes much easier to make changes such as:

  • Switching between a test database and a production database

  • Updating a connection to point to a different server

  • Updating a connection to use a different query string

  • Sharing a connection among multiple PivotTables

With the introduction of CUBE functions in Excel 2007, the need to maintain connections becomes even greater, as you must specify the connection to a particular OLAP cube in each of the CUBE functions. Additionally, one of the challenges for user in previous versions of Excel was that changing connections for query tables and PivotTables was too difficult. Centralizing connection information in one location addresses this need.

Conclusion

Working with external data is a common task in many spreadsheets. Excel 2007 now centralizes connection information to external data that makes it easy to create and manage those connections. Likewise, it is now simpler to specify how the data is to be displayed and where it is inserted.

Additional Resources

For more information about working with Excel 2007, see the following resource: