Using External Data with Microsoft Excel

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Macintosh, versions 5.0, 5.0a

SUMMARY

When you use Microsoft Excel, you can access data from an external database in one of three ways:

• Using the Microsoft Query add-in to return data to a worksheet
• Creating a PivotTable that uses external data
• Using the ODBC add-in in a macro

MORE INFORMATION

Using the Microsoft Query Add-In to Return Data to a Worksheet

With the Microsoft Query add-in (Xlquery.xla) you can bring data directly from an external data source into Microsoft Excel. This add-in uses Dynamic Data Exchange (DDE) with Microsoft Query to allow you to interactively create a query for your external data. You can even the record a macro that retrieves external data using this add-in. To use this add-in, click Add- Ins on the Tools menu and select the MS Query Add-In check box.

When you install the Microsoft Query add-in, the Get External Data command is added to the Data menu. To return data to Microsoft Excel:

1. In Microsoft Excel, click Get External Data on the Data menu to start Microsoft Query.

2. Create the query to achieve the result set you need.

3. On the File menu in Microsoft Query, click Return Data To Microsoft Excel.

4. You will be returned to Microsoft Excel and will be prompted for options on how the data should be returned to Microsoft Excel.

To modify a query that is saved with your worksheet, do any of the following:

• Double-click any cell in the result set.

      -or-
  

• Select any cell in the result set. Click the Get External Data command on the Data menu, and then click the Edit Query button.

   Query

For more information about using Microsoft Query to retrieve data from external databases, click the Index tab in Microsoft Excel 7.0 Help, type the following text

   Microsoft Query

Creating a PivotTable That Uses External Data

The Microsoft Query add-in also allows you to create a PivotTable with an external data source. The PivotTable allows you to summarize external data on your worksheet. To use external data in a PivotTable:

1. On the Data menu, click PivotTable.

2. In Step 1 of the PivotTable Wizard, choose External Data Source, and click the Next button.

3. In Step 2 of the PivotTable Wizard, click the Get Data button.

4. Microsoft Query will be started. Create the query to achieve the result set that you need.

5. On the File menu in Microsoft Query, click Return Data To Microsoft Excel.

6. You will be returned to the PivotTable Wizard in Microsoft Excel.

To update the query results in the PivotTable, select any cell in the PivotTable and click Refresh Data on the Data menu.

To modify the query that is used in the PivotTable, select any cell in the PivotTable and click the PivotTable command on the Data menu. Click the Back button in Step 3 of the PivotTable Wizard and then click the Get Data button in Step 2.

Using the ODBC Add-in in a Macro

You can use the ODBC add-in (Xlodbc.xla) to work with external data programmatically. This add-in allows you to execute queries on the data source, return information about the database, and return data to the worksheet. This add-in does not use Microsoft Query to access the data. The add-in strictly uses the ODBC (open database connectivity) API (application programming interface) to interact with the ODBC Manager and the external database.

To use this add-in a macro, you must reference it from the module sheet. To reference the add-in, activate your module sheet, click References on the Data menu, and select XLODBC.XLA.

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE-ID: Q124218
   TITLE     : XL5: Macro Examples Using XLODBC Functions

Using DAO (Data Access Object) in a Macro (Microsoft Excel 7.0 only)

With Microsoft Excel version 7.0, DAO is the preferred method for working with external data in a macro. In many cases, DAO has much higher performance than the ODBC add-in, provides more functionality and is easier to use. Using DAO, you can access Jet databases, ISAM (indexed sequential access method) databases, and ODBC databases. DAO, like the XLODBC.XLA add- in, does not use Microsoft Query to access the external data.

To use DAO, you must reference it from the Module sheet. To reference the object library, activate your module sheet, click References on the Data menu, and select Microsoft DAO 3.0 Object Library.

For more information about DAO, click the Index tab in Microsoft Excel Help, type the following text

   data access in DAO