Microsoft KB Archive/97732

{| = Excel: Extract Range Not Cleared when Using External Database =
 * width="100%"|

Last reviewed: September 12, 1996

Article ID: Q97732 The information in this article applies to:
 * Microsoft Excel for Windows, versions 3.0, 4.0

SUMMARY
When you extract data from an external database that is not a Microsoft Excel database (for example, a dBASE [.DBF] file or a Q+E query [.QEF] file), the cells under the extract range will not automatically be cleared.

Note: To access an external database that is not a Microsoft Excel database, you must load the QE.XLA add-in macro.

Workaround
If are not using a macro to extract data, select the records that are displayed beneath the extract range, and choose Clear from the Edit menu before each extraction. This procedure will clear the contents of the cells in your extract range.

If you are using a macro to extract the data, you can use the OFFSET function along with the DCOUNTA and COLUMNS functions to select and clear the previously extracted data, as in the following sample macro:

A1: Clear_Extract A2: =SELECT(OFFSET(!Extract,1,0,DCOUNTA(!Database,,!Criteria), COLUMNS(!Extract))) A3: =CLEAR(1) In the sample macro above, cell A1 contains the name of the macro. The formula in cell A2 selects the extracted records in the extract range; in this formula, the DCOUNTA function finds the number of rows to select and the COLUMNS function determines the number of columns to select. The formula in cell A3 clears the contents of the selected cells.

MORE INFORMATION
When you extract data from a Microsoft Excel database, the cells in the extract range are cleared to the bottom of the spreadsheet (even if no information is extracted into them). These cells are automatically cleared if the database is located on same spreadsheet as the extract range or if it is an external Microsoft Excel database.

However, if you are using a non-Microsoft Excel database, such as a dBASE or Q+E database, the cells in the extract range are not cleared. When you use these databases, the extracted records will overwrite whatever is presently showing in the extract range, but the other cells in the extract range will not be cleared. For example, if you have previously extracted 20 records, and then perform another extract that only contains 5 records, the first 5 records will be replaced with the results from the second extract, but the previous 15 records will remain in the extract range.