Number Of Worksheets in an Excel workbook

User avatar
Argus
GoldLounger
Posts: 3081
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

Leif wrote:
13 Jul 2021, 05:15
I hope nobody took my claim of 55,448 worksheets seriously...
:sneaky: for a split second.
:laugh:
Byelingual    When you speak two languages but start losing vocabulary in both of them.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

StuartR wrote:
12 Jul 2021, 22:08
I tried running that and it took about 6 hours to add 3,000 worksheets. I stopped it at that stage.
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
Your six-hour run: Were you saving the workbook after each additional worksheet?

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
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

Leif wrote:
13 Jul 2021, 05:15
I hope nobody took my claim of 55,448 worksheets seriously...
You RAT! :hairout: :aflame: :argue: :bananas: :bash: :bif:

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
An expensive day out: Wallet and Grimace

User avatar
Leif
Administrator
Posts: 7192
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

Running your code, 55,448 worksheets in 31 minutes would have been quite impressive though... :grin:
Leif

User avatar
Argus
GoldLounger
Posts: 3081
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

Ah, there's room for improvements; I see a subculture of sheet mining coming. :grin: 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.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Number Of Spreadsheets in an Excel workbook

Post by StuartR »

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


User avatar
Argus
GoldLounger
Posts: 3081
Joined: 24 Jan 2010, 19:07

Re: Number Of Spreadsheets in an Excel workbook

Post by Argus »

StuartR wrote:
13 Jul 2021, 12:16
It seems a strange coincidence that my laptop gives up at 5448 worksheets too. (It only takes a few minutes without the save).
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. :smile:

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.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

StuartR wrote:
13 Jul 2021, 12:16
It seems a strange coincidence that my laptop gives up at 5448 worksheets too.
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.
(It only takes a few minutes without the save).
This is re-assuring!
Cheers
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number Of Spreadsheets in an Excel workbook

Post by HansV »

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.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

HansV wrote:
13 Jul 2021, 13:33
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.
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
An expensive day out: Wallet and Grimace

User avatar
Leif
Administrator
Posts: 7192
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

I can make it confirmation #4, but why I bother, I really don't know... :grin:
Leif

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number Of Spreadsheets in an Excel workbook

Post by HansV »

Alright, others can simply post Me5448
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Number Of Spreadsheets in an Excel workbook

Post by jstevens »

StuartR wrote:
12 Jul 2021, 22:08
I tried running that and it took about 6 hours to add 3,000 worksheets. I stopped it at that stage.
Here is a tweak to the code which should get you there sooner (or later) :scratch: 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

User avatar
Leif
Administrator
Posts: 7192
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Number Of Spreadsheets in an Excel workbook

Post by Leif »

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

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Number Of Spreadsheets in an Excel workbook

Post by HansV »

Leif wrote:
13 Jul 2021, 15:02
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...?
Done for the first post in the thread. Hopefully, that'll be sufficient.
Best wishes,
Hans

User avatar
Leif
Administrator
Posts: 7192
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Number Of Worksheets in an Excel workbook

Post by Leif »

Thanks! :thumbup:
Leif

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Number Of Worksheets in an Excel workbook

Post by StuartR »

Number of worksheets in an Excel spreadbook?
StuartR


User avatar
Leif
Administrator
Posts: 7192
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Number Of Worksheets in an Excel workbook

Post by Leif »

:laugh:
Leif

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Number Of Spreadsheets in an Excel workbook

Post by ChrisGreaves »

jstevens wrote:
13 Jul 2021, 14:54
Here is a tweak to the code which should get you there sooner...
Now we're Cooking With Gas!
A faster way to find out a universal truth we all already know.
I :salute: you!
Cheers
Chris
P.S. I didn't know about the parameter to Worksheets.Add; thanks; C
An expensive day out: Wallet and Grimace

User avatar
hlewton
PlatinumLounger
Posts: 3785
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Number Of Spreadsheets in an Excel workbook

Post by hlewton »

HansV wrote:
13 Jul 2021, 15:27
Leif wrote:
13 Jul 2021, 15:02
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...?
Done for the first post in the thread. Hopefully, that'll be sufficient.
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