Using External Data with Microsoft Excel

ID: Q141227

The information in this article applies to:

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 the Macintosh, version 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

The "More Information" section of this article describes these methods in detail.

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:

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

Create the query to achieve the result set you need.

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

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

One of the options that you can select is Keep Query Definition. If you select this option, the query is saved with the workbook so that the query can be updated. To update the query results on your worksheet, select any cell in the result set and click Refresh Data on the Data menu.

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.

For more information about "The Setup You Need to Retrieve Data with Microsoft Query" in Microsoft Excel 5.0, choose the Search button in Help, and type:


   Query

Click the Show Topics button, select the topic, and click Go To.

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

double-click the selected text and then double-click the "Retrieving data from external databases" topic.

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:

On the Data menu, click PivotTable.

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

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

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

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

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

To complete the PivotTable, complete the steps in the wizard.

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:

Q124218 : 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

double-click the selected text and then double-click "Data Access Objects Overview."

Additional query words: 5.00a 5.00c pivot table


Keywords          : kbole kbtool xlquery 
Version           : WINDOWS:5.0,5.0c,7.0; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        :

Last Reviewed: April 14, 1999