TransferSpreadsheet to Excel yeilds data + empty cells

[Cornfuzed]

Using transferspreadsheet in Access 2000 to export a
select query to an existing Excel 2000 worksheet. The
export replaces the data in the Excel sheet, but the
number of records (consisting of exported data + empty
cells) is the same as the previous export.

Previous export = 15,000 records (= 15,000 rows in Excel)
new select query = 12,000 records
transferspreadsheet to same Excel sheet
Excel contains the new 12,000 records, but also contains
300 empty cells.

Thanks for the help.

 

[John Nurick]

Hi Corn,

I don't know all the details, but it seems that this happens because
Excel monitors the "UsedRange" of the worksheet, which is roughly
speaking the range starting at A1 and including every cell that contains
a value or has been formatted.

When Access imports or links to a worksheet, by default it assumes you
want to link to the entire UsedRange: hence the empty records.

See http://www.contextures.com/xlfaqApp.html#Unused for instructions on
resettign the UsedRange.