I know that Excel 97 limits workbooks to 255 worksheets but according to the
Help files, Excel 2003 is only limited by system memory/resources.

I created a little test jig that looped on the following statement:

activeSheet.copy , Worksheets("InitialSheet")

In my first test, the InitialSheet had 14 columns x 63 rows (880 cells), 5
command buttons and an image. In this test, I was able to produce 163
copies before Excel started complaining about not being able to find my temp
directory. My code monitored Application.MemoryUsed and it appears that
each worksheet used about 32.5k bytes.

In my second test, the InitialSheet had 14 columns x 63 rows (880 cells), 0
command buttons and no image. In this test, I was able to produce 267
copies before Excel died with an exception. In this case, each worksheets
appears to use about 16.1k bytes.

I also changed my code to only loop 100 times and then reran the code three
times to see if giving Excel a "breather" would help, but that didn't change
anything.


Does this limit "feel" right to people? It seems like I should be able to
have more worksheets than this. My system is running WinXP and Excel 2003
(both have all service packs and patches installed). The system has 512mb
of ram and a 768mb paging file.

TIA

josh

Not enough information here - what else was running on the system and what
could you see in Task Manager?

I would suggest amending your loop code to add in "sleep" after say 250
sheets added and then after each subsequent sheet, so you can have time to
look at Task Manager.

More broadly, if you are using XP and Excel 2003, I think that 512Mb RAM is
not enough for Excel to really fly.

--
www.alignment-systems.com


"Josh Sale" wrote:

I know that Excel 97 limits workbooks to 255 worksheets but according to the
Help files, Excel 2003 is only limited by system memory/resources.

I created a little test jig that looped on the following statement:

activeSheet.copy , Worksheets("InitialSheet")

In my first test, the InitialSheet had 14 columns x 63 rows (880 cells), 5
command buttons and an image. In this test, I was able to produce 163
copies before Excel started complaining about not being able to find my temp
directory. My code monitored Application.MemoryUsed and it appears that
each worksheet used about 32.5k bytes.

In my second test, the InitialSheet had 14 columns x 63 rows (880 cells), 0
command buttons and no image. In this test, I was able to produce 267
copies before Excel died with an exception. In this case, each worksheets
appears to use about 16.1k bytes.

I also changed my code to only loop 100 times and then reran the code three
times to see if giving Excel a "breather" would help, but that didn't change
anything.


Does this limit "feel" right to people? It seems like I should be able to
have more worksheets than this. My system is running WinXP and Excel 2003
(both have all service packs and patches installed). The system has 512mb
of ram and a 768mb paging file.

TIA

josh