Exporting to Excel

[Chris Gorham]

Hi,

I'm exporting the result of a querie into Excel using TransferSpreadsheet.
Clearly I'm limited by the number of rows in Excel. I can detect the number
of required rows using Dcount but then I would like to dump the balance of
the data into the next sheet in the same Excel workbook. How can I acheive
this...??

I use Access 2000.

Thks....Chris

 

[Nikos Yannacopoulos]

Chris,

I guess the general idea would be something along the lines of:

1. count the records, and if > 65536 then
2. use a TOP values query to export the first batch, and read the max
value of your PK field
3. use another query to filter the first on PK > max in previous step,
again check for recordscount > 65536, if yes use TOP values, export etc.

The problem is macros are not very efficient at looping; this kind of
thing - though not impossible through a macro - can be done much more
efficiently with some VBA code.

HTH,
Nikos