How to limit records while exporting data to Excel

  • Thread starter Thread starter subbu
  • Start date Start date
S

subbu

Hi,
I have the requirement to limit the number of rows in
Excel.I am using MSAccess as Backend.Which Excel property
does support this

Any help in this regard is highly appreciated

subbu
 
Thanks for the suggestion.I try transferspreadsheet
function and limit the number of records by using the top
command.
Pl share the code to write to the excelsheet if the
transferspreadsheet does not work

subbu

-----Original Message-----
Hi subbu,

AFAIK there are only two ways of doing this:

1 - Ensure that the query you're exporting doesn't return too many rows.
How to do this will depend on your circumstances; it could be as simple
as using
SELECT TOP 65536 * FROM MyTable;

2 - Write VBA code that uses recordset operations and Automation to
export individual records to successive rows in a worksheet. Have the
code count the rows, and when you get to 65536 or whatever other number
you want, start another worksheet.

Hi,
I have the requirement to limit the number of rows in
Excel.I am using MSAccess as Backend.Which Excel property
does support this

Any help in this regard is highly appreciated

subbu

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
 
A similar topic came up in this newsgroup recently. Search (if necessary
at http://groups.google.com) for messages on 22 July with subject
Export To Excel with more than 65,000 records

The original question there concerned exporting to textfiles which could
then be imported to Excel. It woudn't be hard to modify the code I
posted in the other thread to use Automation to

-open Excel and a new workbook

-instead of opening a text file for output, add a worksheet to the
workbook

-while iterating through the Access recordset, write the data to a row
in the worksheet instead of to a line in the output file.

For information on automating Excel, see

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

Using Automation to Create and Manipulate an Excel Workbook
http://support.microsoft.com/?id=210148



Thanks for the suggestion.I try transferspreadsheet
function and limit the number of records by using the top
command.
Pl share the code to write to the excelsheet if the
transferspreadsheet does not work

subbu

-----Original Message-----
Hi subbu,

AFAIK there are only two ways of doing this:

1 - Ensure that the query you're exporting doesn't return too many rows.
How to do this will depend on your circumstances; it could be as simple
as using
SELECT TOP 65536 * FROM MyTable;

2 - Write VBA code that uses recordset operations and Automation to
export individual records to successive rows in a worksheet. Have the
code count the rows, and when you get to 65536 or whatever other number
you want, start another worksheet.

Hi,
I have the requirement to limit the number of rows in
Excel.I am using MSAccess as Backend.Which Excel property
does support this

Any help in this regard is highly appreciated

subbu

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top