EXCEL monster workbook with hundreds of worksheets

[peck]

We've developed a VBA macro that reads any number of objects (usually business data tables), opens a new sheet for each object read, then copies all/selected data to the new sheet. There are no formulas/dependencies inside the dynamically built sheets nor any across them, except for a single permanent 'index' sheet that contains a list of hyperlinks to the dynamically built sheets. The application works (and performs) very well. EXCEPT in cases where the sheets reach a certain number (approximately 900). Then there is a sudden & dramatic drop in performance. We've tested the app on different processors with different amounts of RAM but problem persists. It would also appear as if EXCEL uses memory to a certain level then ignores remaining available memory. Would appreciate any advice.

 

[carpy1985]

can you organise the datasets better? 900 sheets in one workbook is A LOT!

Who even looks at all those pages? if they do can they be split into sections? so one workbook for Products, another for services, etc???

if the data is in the same format on each tab could you not have a descrition in column a of the data and then paste one under the other starting in column B (not knowing the amount of rows but 1 million in excel 2007+ per sheet is available)

--------A----------------------------------B---------------------------------------C-----------------------------------------
Data set 1----------------------------Blah------------------------------------More Blah-----------------------------------
Data set 1----------------------------Blah------------------------------------More Blah-----------------------------------
Data set 2----------------------------Blah------------------------------------More Blah-----------------------------------
Data set 2----------------------------Blah------------------------------------More Blah-----------------------------------
Data set 3----------------------------Blah------------------------------------More Blah-----------------------------------

 

[peck]

Thank You, carpy1985. Much appreciated.
We've moved on since then (2 years & five weeks ago!). With 64-bit computing & big memory we now open tens of thousands of sheets in the same workbook.
And you're absolutely right: That is A LOT!
BUT is it TOO MANY? I chat about this below.
To share some of our experience:
1. The MS Office Programs have to be activated! When we stepped up to a much faster/bigger configuration, we found that Excel wasn't playing along for some reason (specifically, it wasn't using available memory). Until our admin guy casually commented that the MS Office suite had not yet been activated. Once done, it was all systems go!
2. On just about every thread relating to sheet limit in Excel, tab limit in Excel, maximum sheets in Excel, etc. (its quite possible that we've read all of them), some wiseguy (please don't take it personally, carpy1985) invariably kills the discussion with a comment to imply that 'you may not be doing things in the best way here'.
3. While the above may be true in many cases, it side-steps the technical question being raised
4. For our part, we would like to see Excel tuned for very large numbers of sheets; Currently, we can spawn 5,000 additional blank sheets on a Workbook in about 30 secs (Yes, with the standard 'performance tips' applied.) Why should it take that long. We would like to have it done in less than a second. (Then, with each tranche of 5,000 it becomes slightly slower.)
5. A separate discourse is required to reflect on how EXCEL has become entrenched, how business users interact with it, the psychological difference between having (and changing/comparing/verifying/approving) content in a Wookbook in front of you AND having to do same while the content is remote, etc. etc.
6. We believe we've understood some of this; As a result, we've been able to deliver step changes in productivity;
In conclusion, I've copied a little macro (below) that we use for testing: How long does it take to add 5,000 additional sheets. Try it! I hope it helps to keep a technical focus on a valid technical question. For my part, I wouldn't necessarily second guess anyone's reasons for having vast numbers of sheets in a workbook. Moreover, we should all know by now that even average EXCEL users do much more with data than just LOOK 'at all those pages'.
NB: Save and/or close any open workbooks before executing the macro below. Ensure that the EXCEL VBA window is closed before execution. Don't start off trying to add 5000 sheets. Reduce the number to 500, then increase according to the resources available on your computer. You will notice a drastic slowdown when resources are spent, or there is another obstruction. It may be necessary to forcefully terminate EXCEL, hence ensure that you save and/or close any work.
Dim StartTime As Double, I As Long, J As Long
Sub NewSheets()
' Save the current state of Excel settings.
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
' Turn off UI responses
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' Add 5000 new sheets
I = Sheets.Count
J = I + 5000
StartTime = Timer
While I < J
Sheets.Add After:=Sheets(I)
I = I + 1
Wend
' Restore Excel settings to original state.
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
' Msg to indicate duration
MsgBox ("Elapsed Time: " & Format(Timer - StartTime, "###0.00") & " secs")
End Sub

 

[Kyle123]

I'd have thought that adding the data to the workbook would be much faster using a proper language and doing it XML rather than automating Excel. You don't need to open Excel to write data and VBA is slow. If you really NEED to do this and improve performance, then try doing it in another language. Though how long it will take Excel to open the workbook is another question entirely.

Oh and BTW, I'm most definitely in the WiseGuys' camp here

 

[carpy1985]

You would be surprised at how many times in my work I have seen a similar query with the solution being my original suggestion...

Interesting to read your problems and solutions though as quite often you don't find out what the solution was


Although again, don't rule out someone suggesting you are doing things in the most efficient way... Like above xml maybe a solution you didn't think of


My theory is that I haven't done something the best way until I can't prove otherwise ☺

 

[peck]

Thanks All.

Are you saying that if we triggered the equivalent of multiple 'add Sheet' instructions in a different language/way it would improve performance significantly? Would appreciate more detail then, perhaps sample code.

 

[Kyle123]

No, I'm saying that since the xlsx file format adheres to the Open Office XML standard, you can build excel files in raw XML (if you want to have a look what this looks like then change an excel file extension from .xlsx to .zip and have a dig), there is a Microsoft SDK for helping here Welcome to the Open XML SDK 2.5 for Office, but you might be better off with something a bit more high level (there are loads of libraries for working with Excel files).

Since you are only creating XML files, you aren't having to deal with the Excel object model which is notoriously slow. You're also going to need to ditch VBA, it's slow and only works in an Excel environment, the language choice is up to you, though you'll probably have most luck with C# or Java since most Excel work is done in corporate environments (though there will be Excel libraries for most popular languages).

 

[Ragnar1211]

Can you provide some insight as to why you need 1000's of worksheets in a workbook? You can handle large amounts of data externally and summarize the details within excel.
I cannot imagine trying to navigate such a beast.

Is this workbooks being used by end users or is it just used to dump data?

 

[peck]

Thanks Kyle123.

See my orginal post. This is an interactive process. The user starts with a blank Workbook. A set of VBA macros (which we would be happy to re-write as something else if it will make 'add Sheet' happen significantly faster) then allows the user to look up different data objects across the enterprise. As the user does this, more sheets are added to accomodate each object. This is happening inside the very Workbook the user is looking at. The user can continue 'calling in' data from various places. So, we're not in a position to generate a new workbook in raw XML each time.

 

[Kyle123]

Sounds like you need some BI software