OutputTo - Excel limitation ?

[prasadmokashi]

Hi,

I am running sybase stored procedure using pass-through query and want to send the output to excel worksheet.

DoCmd.OutputTo acOutputQuery, "runSP_VolumeCompare", acFormatXLS, sFileName

Query returns around 30,000 records. I get following error when I try to send that output to excel worksheet.

"There are too many rows to output, based on the limitation specified by the output format or by Access."

I guess, it is the limitation from excel side.

Any idea how to overcome this ?

Thanks,
Prasad

 

[KenReay]

Hi

I am not an Excel expert, but I believe max number of rows in an Excel spreadsheet is 8192, may be greater in later versions,

only way I can think of to overcome the limit is to split your returned dataset into 8182 row chunks and output each to a seperate spreadsheet

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Molby]

Current Versions of Excel have 65,536 rows, prior version to Excel 97 had only 16,384 rows.

 

[KenReay]

Hi

There you go,told you I was not an expert, so if you need > 30000 rows looks like you need to upgrade to latest version (2003)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[prasadmokashi]

Thank you very much for your responses.

But I am using latest version Access 2003 and still getting the error. :-(

Regards,
Prasad

 

[RoyVidar]

Would it change anything if you changed the object type to acOutputStoredProcedure?

Else, if you're returning a recordset, could you perhaps use the .CopyFromRecordset method of the Excel Range object (would require opening/creating an instance of Excel and passing the recordset)?

Roy-Vidar