Introduction to new functions of Excel 12 (1)

Use Excel 12 to manage external database connections

Microsoft has made a series of breakthroughs in "connection management" in Excel 12 workbooks. Specifically, three new features can be implemented in workbooks connected to external data, whether external data is an Access database, an Oracle database, or an analytic service multi-dimensional dataset ). In Excel 12, users can: 1. view the list of connections used in the workbook; 2. View where these connections are used; 3. Change the connection attributes.

Because the word "connection" has different meanings for different people, especially developers, we may first explain what "connection" means for Excel. The basic definition is as follows: "A connection is a unique way to identify external data and the information required to connect to the external data source so that some or all external data can be imported to Excel 12 ." In other words, the connection is just information-which type of connection is used, such as ODBC), what is the server name, such as MyRelationalServer, and what is the table name, such as MyTable) optional parameters, such as Persist Security Info = True. Excel uses this information to obtain data for Excel, such as sales data, inventory data of a company, or any other data that users may want to import to Excel ).

In the Excel environment, the connection information can be saved in the Excel Workbook through the so-called "workbook connection", or saved in your own file. We call this file "connection file ". The connection file created in Microsoft Office is assigned with the file extension. odc, which represents "Office data connection ". Excel can open the. odc file and establish a connection based on the file content. If a connection file is used by Excel 12, all connection information will be copied from the file to the Excel 12 workbook to establish a workbook connection. Then, Excel will copy its own connection information to obtain data from external data sources. If you use an Excel tool to connect to an external data source, Excel will connect to the workbook to save the connection information. In general,. the odc file is stored in the "My Data Sources" folder. The idea is that the next time you want to connect to the same data source, you don't have to re-enter all the connection information in the dialog box as long as you open the file. Note: This section does not introduce new features specific to Excel 12, which are also available in the current version of Excel.

Many abstract terms are involved here, so we have summarized the following entries:

● External data sources-independent databases or data files;

● Connection information-information required to connect to external data;

● Connection file-an independent file containing connection information;

● Workbook connection-copy the connection information in the workbook.

Now let's take a look at the workbook connection in Excel 12, which is a new part ). In Excel 12, Microsoft added a new dialog box to indicate all existing workbooks in the current workbook. You can enter the dialog box from the "Data" Data) tab and click "Connect" Connections ). (1)

Workbook connection page in Excel 12

If there is no connection in the current workbook, the displayed dialog box is blank (2 ).

Empty dialog box without connection

If there are several connections in the workbook, it is like this (see ).

Connected dialog box

Note: Each connection in the workbook has a unique name that is unique to the workbook ). You only need to select a connection and click the link in the lower part of the dialog box to see where the connection is used.

You can also learn more about a connection. Just select the connection in the upper part of the dialog box and click "properties ..." Properties ...) . The "Connection Properties" Connection Properties dialog box is displayed (4 ).

Connection Properties dialog box

This dialog box has two labels. The first label marked as "use" Usage) is displayed in front of the workbook. The parameters in the label indicate how to connect to the workbook. After the second label is displayed, the parameter in the label defines the connection information (5 ).

Function labels of the Connection Properties dialog box