I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at
http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory (default is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to "Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.

just create a workbook with 100 sheets

select all sheets

type1 in a1
type1 in e10000

select sheet1

save

look at size.

nuf said.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


deko wrote :

I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at
http://office.microsoft.com/en-us/as...033.aspx?mode=
print and the Maximum limit is defined as "Limited by available
memory (default is 3 sheets)". Is this true only for XL2003? What
about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.

select all sheets

type1 in a1
type1 in e10000

select sheet1

save

look at size.

For some reason that didn't work for me.

But I tried this:

right click on worksheet tab
select all sheets
right click on any worksheet tab and select insert
click OK on Insert dialog
open immediate window
debug.Print worksheets.Count
repeat

Results:

Original number of sheets: 60
Original size: 8.12Mg

debug.Print worksheets.Count
120
debug.Print worksheets.Count
240
debug.Print worksheets.Count
480
debug.Print worksheets.Count
960

At 960 the size was 9.51Mg - after inserting 900 empty worksheets.
Performance was acceptable, but somewhat slow opening and saving.

If I inserted copies of the existing worksheets, my guess is the size would
be just over 100Mb. What I'd like to do is find a way to write a loop that
would copy/rename/insert a particular worksheet in the workbook, and test
again at the above intervals. That way I'd know what the practical ceiling
is for my app.

Any suggestions on how to construct such a loop?

You might check out Charles Williams' sight:

http://www.decisionmodels.com/memlimits.htm

--
Regards,
Tom Ogilvy


"deko" wrote in message
m...
select all sheets

type1 in a1
type1 in e10000

select sheet1

save

look at size.

For some reason that didn't work for me.

But I tried this:

right click on worksheet tab
select all sheets
right click on any worksheet tab and select insert
click OK on Insert dialog
open immediate window
debug.Print worksheets.Count
repeat

Results:

Original number of sheets: 60
Original size: 8.12Mg

debug.Print worksheets.Count
120
debug.Print worksheets.Count
240
debug.Print worksheets.Count
480
debug.Print worksheets.Count
960

At 960 the size was 9.51Mg - after inserting 900 empty worksheets.
Performance was acceptable, but somewhat slow opening and saving.

If I inserted copies of the existing worksheets, my guess is the size
would
be just over 100Mb. What I'd like to do is find a way to write a loop
that
would copy/rename/insert a particular worksheet in the workbook, and test
again at the above intervals. That way I'd know what the practical
ceiling
is for my app.

Any suggestions on how to construct such a loop?

sight = site

hopefully he can see fine.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
You might check out Charles Williams' sight:

http://www.decisionmodels.com/memlimits.htm

--
Regards,
Tom Ogilvy


"deko" wrote in message
m...
select all sheets

type1 in a1
type1 in e10000

select sheet1

save

look at size.

For some reason that didn't work for me.

But I tried this:

right click on worksheet tab
select all sheets
right click on any worksheet tab and select insert
click OK on Insert dialog
open immediate window
debug.Print worksheets.Count
repeat

Results:

Original number of sheets: 60
Original size: 8.12Mg

debug.Print worksheets.Count
120
debug.Print worksheets.Count
240
debug.Print worksheets.Count
480
debug.Print worksheets.Count
960

At 960 the size was 9.51Mg - after inserting 900 empty worksheets.
Performance was acceptable, but somewhat slow opening and saving.

If I inserted copies of the existing worksheets, my guess is the size
would
be just over 100Mb. What I'd like to do is find a way to write a loop
that
would copy/rename/insert a particular worksheet in the workbook, and
test
again at the above intervals. That way I'd know what the practical
ceiling
is for my app.

Any suggestions on how to construct such a loop?

http://www.decisionmodels.com/memlimits.htm

Interesting stats. Bottom line: the bigger the workbook, the more RAM you
need.

what's happening in my case is an export from Access creating any number of
worksheets - depending on how many data files the user points the app at.
The app can run for 30 or so minutes on slower machines when creating 120
worksheets. Each worksheet has at least one chart (and some have up to 10
charts) so I'm worried about users creating workbooks so big they can't open
them - and blaming my app. Perhaps I should impose a 255 worksheet limit...

The number of sheets in a new workbook is 255 (You set this he Tools
Options: General, "Sheets in new workbook") , but you can then add more
sheets until your system's resources run out. This has been the case since
Excel 95.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"deko" wrote in message
...
I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at

http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory (default
is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.

The number of sheets in a new workbook is 255 (You set this he Tools
Options: General, "Sheets in new workbook") , but you can then add more
sheets until your system's resources run out. This has been the case since
Excel 95.

Silly me. I export to Excel from Access and had written code to handle
this...

If z 254 Then Err.Raise WORKBOOK_FULL

Hi

This spring I was handed a workbook with 1600 worksheets in it, each sheet
containing a chart. I volunteered to reorganize that work, believing that
the thing would self destruct at a point very soon. While I rewrote it, they
continued to use it.

It died while adding sheet number 2005 into it. So the limit is either 2004,
or maybe "last year". Depends on the content of course. But the point is,
that file will not be fine again just with more RAM, it killed itself
because of its size. So don't push those limits.

HTH. Best wishes Harald

"deko" skrev i melding
...
I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at

http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory (default
is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.

And when you finished your rewrite, you had how many sheets?

Did you create the charts on the fly or some other approach?

Signed,

Just Curious

Harald Staff wrote:

Hi

This spring I was handed a workbook with 1600 worksheets in it, each sheet
containing a chart. I volunteered to reorganize that work, believing that
the thing would self destruct at a point very soon. While I rewrote it, they
continued to use it.

It died while adding sheet number 2005 into it. So the limit is either 2004,
or maybe "last year". Depends on the content of course. But the point is,
that file will not be fine again just with more RAM, it killed itself
because of its size. So don't push those limits.

HTH. Best wishes Harald

"deko" skrev i melding
...
I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at

http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory (default
is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.



--

Dave Peterson

Hi Julius Curius

One sheet an one chart for processing, one userform for controlling the
thing and an Access mdb file containing all data. The best of three worlds
<g

Best wishes Harald

"Dave Peterson" skrev i melding
...
And when you finished your rewrite, you had how many sheets?

Did you create the charts on the fly or some other approach?

Signed,

Just Curious

Harald Staff wrote:

Hi

This spring I was handed a workbook with 1600 worksheets in it, each
sheet
containing a chart. I volunteered to reorganize that work, believing
that
the thing would self destruct at a point very soon. While I rewrote it,
they
continued to use it.

It died while adding sheet number 2005 into it. So the limit is either
2004,
or maybe "last year". Depends on the content of course. But the point
is,
that file will not be fine again just with more RAM, it killed itself
because of its size. So don't push those limits.

HTH. Best wishes Harald

"deko" skrev i melding
...
I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at


http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory
(default
is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.



--

Dave Peterson

Thanks,

I am no longer curious (yellow).



Harald Staff wrote:

Hi Julius Curius

One sheet an one chart for processing, one userform for controlling the
thing and an Access mdb file containing all data. The best of three worlds
<g

Best wishes Harald

"Dave Peterson" skrev i melding
...
And when you finished your rewrite, you had how many sheets?

Did you create the charts on the fly or some other approach?

Signed,

Just Curious

Harald Staff wrote:

Hi

This spring I was handed a workbook with 1600 worksheets in it, each
sheet
containing a chart. I volunteered to reorganize that work, believing
that
the thing would self destruct at a point very soon. While I rewrote it,
they
continued to use it.

It died while adding sheet number 2005 into it. So the limit is either
2004,
or maybe "last year". Depends on the content of course. But the point
is,
that file will not be fine again just with more RAM, it killed itself
because of its size. So don't push those limits.

HTH. Best wishes Harald

"deko" skrev i melding
...
I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at


http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory
(default
is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.



--

Dave Peterson

--

Dave Peterson

Without knowing more, that and perhaps a pivottable might be one solution
for Deko.

--
Regards,
Tom Ogilvy

"Harald Staff" wrote in message
...
Hi Julius Curius

One sheet an one chart for processing, one userform for controlling the
thing and an Access mdb file containing all data. The best of three worlds
<g

Best wishes Harald

"Dave Peterson" skrev i melding
...
And when you finished your rewrite, you had how many sheets?

Did you create the charts on the fly or some other approach?

Signed,

Just Curious

Harald Staff wrote:

Hi

This spring I was handed a workbook with 1600 worksheets in it, each
sheet
containing a chart. I volunteered to reorganize that work, believing
that
the thing would self destruct at a point very soon. While I rewrote
it,
they
continued to use it.

It died while adding sheet number 2005 into it. So the limit is either
2004,
or maybe "last year". Depends on the content of course. But the point
is,
that file will not be fine again just with more RAM, it killed itself
because of its size. So don't push those limits.

HTH. Best wishes Harald

"deko" skrev i melding
...
I've heard that the maximum number of worksheets allowed in an Excel
workbook is 255. Is this urban myth? I just had a look at "Excel
specifications and limits" for XL2003 at



http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available memory
(default
is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard to
"Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The only
limitation is memory/processor?

Thanks in advance.



--

Dave Peterson

The 255 sheet limit is the maximum number of sheets that sheets
allowed in a newly created workbook. It is the upper limit
imposed by the Application.SheetsInNewWorkbook property.

You can add aditional sheets as required by your application.
There is no hard limit to the number of sheets in a workbook. It
is limited by the memory available to Excel.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"deko" wrote in message
...
I've heard that the maximum number of worksheets allowed in an
Excel
workbook is 255. Is this urban myth? I just had a look at
"Excel
specifications and limits" for XL2003 at
http://office.microsoft.com/en-us/as...spx?mode=print
and the Maximum limit is defined as "Limited by available
memory (default is
3 sheets)". Is this true only for XL2003? What about XL2000?

The only thing I've read about chart limitations is in regard
to "Worksheets
referred to by a chart" - which is no an issue for me.

So it's possible to have 500 worksheets and 2000 charts? The
only
limitation is memory/processor?

Thanks in advance.