Access VBa: to check end of excel file while reporting

Question

Hi,

I am writing the results of certain checks on access table to a excel file. Sometimes the results exceed 65k that is more than excel can handle( excel 2002). How do i check the end of the file and open a new sheet for the continuation

Thanks

Answer 1

I should ask how you're outputting these to a file.

If it's procedurally then Why not have a running counter that goes to 65535. When it exceeds that, start a new worksheet.

Answer 2

You have at least two options.

The first is simply count the rows as you're outputting them (if the write doesn't happen all at once, keep a secondary file with the count of rows in the primary file). As the row count approaches the limit (i.e 65k) start a new worksheet.

Depending on how flexable your file structures are, an alternative is to write all the rows to an access table, and then have the Excel sheet query the Access Table. This will get around the row limit in Excel 2002