for a split second.
Number Of Worksheets in an Excel workbook
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
Byelingual When you speak two languages but start losing vocabulary in both of them.
-
- PlutoniumLounger
- Posts: 15585
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
Stuart, this comment puzzled me, for in one of my runs (to 5,500 or thereabouts), I sat at the machine and watched it run, a sure sign that it was a short run, since the day was sunny and there was much fun to be had outside.
So early this morning I ran two tests, one without saving the workbook to disk, and one with.
A document outlining my efforts can be downloaded from http://www.chrisgreaves.com/Downloads/20210713_0848.zip
Without the Save of the workbook, I managed to add 5,450 worksheets in 1m 35s.
With a Save every 100 worksheets (added) This run failed after about 18 minutes with 5,449 worksheets in place.
Code: Select all
1000 0:26
2000 1:46
3000 4:26
4000 8:10
5000 13:29
5100 15:41
5200 16:18
5300 16:57
5400 17:43
5500
I can't believe that my non-SSD 4GB laptop can do it 17 minutes what your superior device cannot achieve in six hours.
If you are pulling my leg, please pull the other one; Leiff has hold of this one and is tugging fiercely!
Cheers
Chris
There's nothing heavier than an empty water bottle
-
- PlutoniumLounger
- Posts: 15585
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
You RAT!
That just cost me another sleepless, night, wondering how my simple "resize" and Shift-Ctrl-X in MSPaint could wreak such havoc to my image!
Cheers
Chris
There's nothing heavier than an empty water bottle
-
- Administrator
- Posts: 7208
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Number Of Spreadsheets in an Excel workbook
Running your code, 55,448 worksheets in 31 minutes would have been quite impressive though...
Leif
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
Ah, there's room for improvements; I see a subculture of sheet mining coming. Using multiple workstation CPUs and all the RAM they can buy.
Byelingual When you speak two languages but start losing vocabulary in both of them.
-
- Administrator
- Posts: 12601
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Number Of Spreadsheets in an Excel workbook
It seems a strange coincidence that my laptop gives up at 5448 worksheets too. (It only takes a few minutes without the save).
You do not have the required permissions to view the files attached to this post.
StuartR
-
- GoldLounger
- Posts: 3081
- Joined: 24 Jan 2010, 19:07
Re: Number Of Spreadsheets in an Excel workbook
Each with 17,179,869,184 (empty) cells.
That's 93,578,747,445,248; still not enough for all IPv6 addresses, we need something else.
https://www.pcreview.co.uk/threads/file ... t-10482748
FWIW, we might categorize this as interesting XL trivia:
(Who would want/need this many sheets?)
Max number of worksheets - 5,447
Trying to insert another to make 5,448 sheets, caused XL to crash.
As tested by Dana in XL07,
And verified by Harlan in XL03.
Byelingual When you speak two languages but start losing vocabulary in both of them.
-
- PlutoniumLounger
- Posts: 15585
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
Stuart, I am certain that you are not on a 4GB RAM machine!
The limitation of 5,448 would seem to be an Excel2003 (and on!) mandated limit, rather like the limit of 9 most-recent files (in 2003)
5448=2x2x2x3x227
I see no immediate value in the prime factors.
This is re-assuring!(It only takes a few minutes without the save).
Cheers
Chris
There's nothing heavier than an empty water bottle
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number Of Spreadsheets in an Excel workbook
5448 here too - Excel 2019 32-bit on Window 10 64-bit with 16 GB RAM.
It took 49 seconds without the ActiveWorkbook.Save line.
It took 49 seconds without the ActiveWorkbook.Save line.
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15585
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
Right! That is the third confirmation here.
Now, who volunteers to telephone the designer and tell him off?
We already know his/her/its telephone number. (" ... If you receive a phone call from area code 222 it is a spam call.")
Cheers
Chris
There's nothing heavier than an empty water bottle
-
- Administrator
- Posts: 7208
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Number Of Spreadsheets in an Excel workbook
I can make it confirmation #4, but why I bother, I really don't know...
Leif
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number Of Spreadsheets in an Excel workbook
Alright, others can simply post Me5448
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2628
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Number Of Spreadsheets in an Excel workbook
Here is a tweak to the code which should get you there sooner (or later) The code adds 100 sheets at a time until a threshold of 5400 is reached then it only adds one sheet at a time.
Code: Select all
Sub MaxWorksheets()
While True
If Worksheets.Count < 5400 Then
Worksheets.Add , Count:=100
Else
Worksheets.Add
End If
Application.Caption = Worksheets.Count
ThisWorkbook.Save
DoEvents
Wend
End Sub
Regards,
John
John
-
- Administrator
- Posts: 7208
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Number Of Spreadsheets in an Excel workbook
I'm just wondering if we should change the subject(s) in this thread to "Number Of Worksheets in an Excel workbook" so that it can be found by future generations...?
Leif
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Number Of Spreadsheets in an Excel workbook
Done for the first post in the thread. Hopefully, that'll be sufficient.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 7208
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
-
- Administrator
- Posts: 12601
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
-
- Administrator
- Posts: 7208
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
-
- PlutoniumLounger
- Posts: 15585
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Number Of Spreadsheets in an Excel workbook
Now we're Cooking With Gas!
A faster way to find out a universal truth we all already know.
I you!
Cheers
Chris
P.S. I didn't know about the parameter to Worksheets.Add; thanks; C
There's nothing heavier than an empty water bottle
-
- PlatinumLounger
- Posts: 3808
- Joined: 24 Oct 2010, 23:39
- Location: Canton, Ohio USA
Re: Number Of Spreadsheets in an Excel workbook
It's no wonder I have not been getting email notifications. I was wondering why. Am I correct in assuming that changing the title caused that? if so, how do I re-subscribe so I get the email notifications again?
Regards,
hlewton
hlewton