Limit number of worksheet rows

[Saenz2008]

Hello,
I am looking for a way to limit the number of available rows per sheet to 1000 so that when I import data that exceed that it will distribute all of the data among different sheets. I have seen some threads that suggest hiding the rows and/or protecting the rows that that didn't work. Hiding just placed the data in the hidden rows and did not distribute to the other work sheets. Protecting cells prevented anything from crossing when the number exceeded 1000.

I am trying to avoid programming options to keep the process simple but if that is the only way to distribute the content then I welcome suggestions.

Thanks, HS

 

[Fazza]

Hello
If using a recordset, the .copyfromrecordset method has a maxrows option - so just set to 1000 (or 999 if including headers).
If not using a recordset you'll need to explain/detail how the import is being done for anyone to be able to assist.
regards

PS One link for info on copyfromrecordset https://msdn.microsoft.com/en-us/library/aa165427(v=office.10).aspx
Google will have lots of info too

 

[Saenz2008]

To be more specific, I am using the Excel file as a driver data source within Business Objects (BO) connected to an Oracle Database. It comes in as a "In list()" which Oracle has a limit of 1000 elements. The actual source of the data and method of import can vary. The Excel could be produced from BO connected to a different data source. I am looking to create an Excel template to use with BO for these type of instances. I remember in prior versions of Excel when I copied data that exceeded the number of rows it placed the content in the other sheets.

I am thinking; however, that that may not be a good solution after all since I would not have the headers. I may just place all the data in the first sheet and put formulas in the subsequent sheets to bring over the 1000 rows per sheet. I do like your suggestion regarding copyfromrecordset and may incorporate that in my solution.

Thanks for your quick response. HS