[RESOLVED] Just Wondering

[billhuard]

How many sheets does a workbook can contain??

MicroSoft says that the number of sheet is limited by the available memory of the system.

I'm about to implement a tools that will have a variable number of sheets and can go easily over 500 sheets.

Have you ever build a workbook with 500 sheets??
Just want to know if it is possible ???

[Hack]

Excel specifications and limits

Open workbooks Limited by available memory and system resources
Worksheet size 65,536 rows by 256 columns
Column width 255 characters
Row height 409 points
Page breaks 1000 horizontal and vertical
Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 56
Cell styles in a workbook 4,000
Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook Limited by available memory
Custom number formats Between 200 and 250, depending on the language version of Excel you have installed.
Names in a workbook Limited by available memory
Windows in a workbook Limited by system resources
Panes in a window 4
Linked sheets Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 3 in a single sort; unlimited when using sequential sorts
Undo levels 16
Fields in a data form 32
Custom toolbars in a workbook Limited by available memory
Custom toolbar buttons Limited by available memory

This is from Excel 2003...it may be different in the version you have (which you didn't specify)

[RobDog888]

500 sounds unmanageable for a user in one workbook. How about breeaking it up into several workbooks depending on your app?

[billhuard]

I'm using Excel 2003 SP1


This workbook won't be used by user.

Here is what I have to do, at large.

Company have 6 buyers
Company have more than 400 items (+/-65 item per buyer)
One sheet per item
One workbook per buyer

Each month buyer exchange some items. So I have to change sheet from workbook. There is an index file where buyer and item are related.

So what I'm doing is, at each month end I'm putting all item sheets in one workbook and after I'm assigning them to there respective buyer(Workbook).


Can it be done?

If answer = yes
thanks for your advise
else
need some idea
End if

[RobDog888]

I dont think you need a sheet per item as you could have all items on a sheet. Then one sheet for all buyers. Each of the two sheets will have a index field where you can link the two together.

[Hack]

This sounds more like a database application than an Excel application.

[RobDog888]

Yup, I got that too.

[billhuard]

One sheet for all items is impossible.
Because there is too much data for each item. Around 1000 row by 25 column of data for each item.

Sounds like a database app for me too, but the analysis tell me to do it only with Excel because the company is currently changing from Windows to AS/400 where database relation is completly different. Also, all data from past year for each item cannot be found on the database, but on Excel file. Buyers always worked with Excel and must continue.

One more thing, the tools is midway from being finish. A consultant started it but his contract expired and at the same time my intership finished. The company choose to keep me in for less $$$ than the consultant. So I have to finish his job.

This is why I wanted advise on how many sheets have you ever putted in a workbook.

[RobDog888]

Ok, I successfully added 5,000 sheets to my workbook but mind you they were all blank sheets. So if they have a powerfull enough system then they can work with 500 no problem. Just the more data on each sheet takes more memory.

Dim i As Integer
    
    For i = 1 To 4997 'Workbook has 3 sheets in it already.
        Application.Workbooks(1).Sheets.Add
        DoEvents
    Next

[Hack]

This is why I wanted advise on how many sheets have you ever putted in a workbook.

Well, the Help file tidbit that I posted should provide you with what you can do.

The most I've ever had was 6. When it looked like I was going to a 7th I scrapped Excel and migrated everything to a database and wrote Excel like frontend to it so all of the users wouldn't freak out too badly.

[RobDog888]

Even with 5,000 bank sheets I did notice a very slight lag with Excel. So you wont know how the users are going to be able to work with it until you test it. Oops, just got a Excel error and the workbook is attempting to recover it. Hmm, could be an issue having so many sheets I guess?

[salvelinus]

Do all the sheets need to be open at once?

[billhuard]

Yeah they need to be all open at once, because once the buyer file is created, it creates un sub-department total, a department total and a lot more sub total et dept total and company total. There is so many that your imagination is not big enought to imagine the half of it.

I'm trying to recreate your loop to copy paste the item sheet.
But I'm always stuck at 256 sheets, the magic the number and the memory usage of my windows task manager is at 20%.

Why it won't continue??

Sub test()
 
Application.ScreenUpdating = False
Dim i As Double
    
    For i = 1 To 4997 'Workbook has 3 sheets in it already.
        Sheets("Class").Copy Before:=Sheets("sheet2")
        Sheets("Class (2)").Name = "test" & i
        DoEvents
    Next
End Sub

thanks for your help guys

[Webtest]

Excel 2003:

I manually created 500 sheets ... Menu > Insert > Worksheet and then hold down <Ctrl>Y ... and then did a couple of tests that worked fine ...

Option Explicit
Sub Macro1()
    Dim aSheet As Worksheet
    Dim tStr As String
    Dim i As Integer
    
    i = 0
    For Each aSheet In ActiveWorkbook.Sheets
        i = i + 1
        tStr = "TEST " & aSheet.Name
        aSheet.Cells(1, "A").Value = tStr
        Debug.Print i, tStr
    Next aSheet

End Sub

Sub macro3()
    Dim i As Integer
    
    For i = 1 To 500
        ActiveWorkbook.Sheets("Sheet" & i).Cells(2, "B").Value = "Test " & i
    Next i
    ActiveWorkbook.Sheets("sheet500").Cells(3, "C").Value = "This is Sheet 500"
End Sub

The file takes a while longer than 'normal' to save (about 70 seconds as opposed to 5 to 10 seconds). The file is less than 1.5 megs and takes about 15 megs of system memory when open.

[billhuard]

WebTest, is your sheets empty?

[Webtest]

Bill:

My sheets are approximately empty ... the macros write text into cells A1 and B2 using 2 different iteration methods. The numbers I gave were after these 2 cells were loaded. I just wanted to see if 256 was any kind of a hard limit.

I have 768 Megs of system memory on this machine.

[billhuard]

Okay,
I'm able to add over 7000 sheets with approx nothing on it.
What I'm not able to do is copy a sheet more thant 256 times. Once I reach to 256 copies, I can add new sheet but cannot copy anymore.

Can you please try to load a sheet(1000row, 25 colunm) with crap in it and copy and paste it as much as you can and tell me if you can reach more tha 256 times.

[Webtest]

Grief ... it took 3 minutes to copy 32 sheets ... I'm copying 64 now. I hope it finishes before it is time to leave! We're up to about 50 megs of system memory.

[Webtest]

This is interesting ... it took up to 100 megs of system memory during the copy, but when it finished, the open book only took about 40 megs. The file size for 128 sheets is 48 megs. I'm copying those 128 to make 256 now. We're up to 120 megs of system memory so far with quite a ways to go.

[billhuard]

Can wait to see if you can more than 256.

Don't forget to turn auto-safe recover off.
I took 3 mins to complete with my 256 sheets workbook. I tought Excel just crashed but to my surprise it didn't.

[Webtest]

Don't forget to turn auto-safe recover off.

What's that?

I'm running a 1.6 GHz P4 in an IBM box with 768 Megs. It took 25 minutes at 100% Processor load and at least 200 Megs of system memory (in addition to the 340 megs for win 2k pro) to copy 128 Sheets.

OK ... I had 256 sheets and then copied sheets 1 - 4 with no problem, for a total of 260 sheets. I just didn't try to do them all at one time. The file size is now 95 Megs. I don't have time for any more "fun & games" tonight ... got to get a few more things done so I can go home. See y'all tomorrow!

[billhuard]

Tools->Options->Save

Save AutoRecover

That is why every time Excel crash, when you reopen Excel is able to give you a workbook partially saved.

By the way, thank for your help & precious time

[Webtest]

Bill:

I started with 260 sheets this morning ... text "Row m Col n" where m is the row # and n is the col # for 1000 rows and 25 columns on all sheets.

I selected sheets 1 - 100 and did a manual copy. It took about 15 minutes at 100% processor, system memory usage peaked at +130 Megs just before it finished. However, after it finishes, the "in use" memory count drops way down. The final file size for 360 sheets is 130 Megs. I can see that copying 500 sheets at a clip would be severly memory intensive (over 600 megs). I couldn't do that on my system if this operation needs physical memory! How much physical memory do you have available on top of your OS? Maybe try copying smaller blocks ... say 50 pages at a time.

[billhuard]

If I'm looking in the Windows Task Manager, I have a Physical Memory(K) Total 522672
Available 259600
System Cache 331640

Am I looking at the right place?

[Webtest]

Yes, you're looking in the right place. It looks like you have '512' Megs ... you're idling along with 263 megs in use, and you have about 260 megs free. For my pages, that means you will run out of memory in less than 200 pages during the copy! Definitely try the smaller blocks approach! Copy maybe as many as 100 at a shot. See if that works.

I think I forgot to mention ... I'm running Win 2k Pro OS.

[billhuard]

Very weird.

I've copied my sheet 256(the max i can)

Physical Memory available 194 216
Memory Usage 290 008
File size 25.5 MB

I Saved the workbook and I've been able to copy another 100 sheets before it stopped again.

Now I have 354 sheets

Physical Memory available 167168
Memory Usage 292080
File Size 36 MB

Now I cannot copy a sheet, even manually and even if I save the workbook.

But I can still add new sheet.

Where is Bill Gates, I have some questions for him and some advise for the next Excel on Vista.???

[TheBionicOrange]

That sounds crazy to have a workbook that large.
I would definately go with the d/b option mentioned near the start of this thread.
Even if you get it to work it sounds like its always something thats going to grow so its only a matter of time before you run into problems. And what if other applications are open at the same time ?
Sounds like a recipe for disaster !!

[Webtest]

Bill:

What is the indication/Error message (if any?) that you get when you manually try to copy one sheet?

[billhuard]

Absolutely nothing. There is no error message but there is no sheet too.
Excel still going like everything is going weel


So stupid...
I have looped over 1000 times before I manually stopped this:
Add a new sheet
Copy my sheet of 1000 row by 25 columns to the new sheet.

With 1000 sheets
Available memory: 65300
Mem Usage 410276

WorkBook took over 4 minutes to save(but I don't need to save my Big workbook)

TheBionicOrange: It is impossible to me to use a DB, the company is moving from windows to as/400. They need the tool by the end of march or so...

[billhuard]

Okay, Stop everyone.

I've slammed my fist on the table and told the project manager, that we were playing "Russian Roulette" with this.

By doing this I resolve a big problem but shooted myself on the foot.
I need to find another way to execute the same thing without using DB and not putting all file in one workbook.

God I'm gonna have fun...

[billhuard]

Thanks to all of you for your help

I really really apprecitate this!!!!

[Webtest]

God I'm gonna have fun...

Do you mean ... like ... 2 weeks in Puerto Vallarta ... on crutches?

[billhuard]

Not really....

I just ordered 2 keyboard and a flat screen to ITtechnician for my next slammin and derby destruction

More this kind of fun

[TheBionicOrange]

Okay, Stop everyone.

I've slammed my fist on the table and told the project manager, that we were playing "Russian Roulette" with this.

By doing this I resolve a big problem but shooted myself on the foot.
I need to find another way to execute the same thing without using DB and not putting all file in one workbook.

God I'm gonna have fun...

I appreciate you have problems not being able to go down the DB route ... but I think you really WERE heading for trouble trying to use Excel !

Good luck with the derby destruction !