Excel: Extract Range Not Cleared when Using External Database

• 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)

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.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, page 341 "Microsoft Excel User's Guide," version 3.0, page 282