Excel crashing when (programatically) adding past the 360th worksheet to a workbook

[Scott Lyon]

I'm working on an application (in Excel, with VBA code behind it). Part of
the process of building this was creating a "template" worksheet (with the
code already with it, although commented-out), and then creating a function
that will duplicate that "template" as needed (ultimately 600-700 additional
worksheets added to the workbook).


The code I have to do that is basically a WHILE loop, referencing data in
another worksheet, incrementing integer intLoop each time, and ending when
it runs out of data on that worksheet. There is also an integer
intBeforeSheet that tells it where to insert the copies. The code to
actually copy the template is as follows:

Sheets("WS_Template").Select
Sheets("WS_Template").Copy Before:=Sheets(intBeforeSheet +
intLoop)


It seems to have been working for the most part, until today. Today, it
seems when it hits the point that there's a total of 360 worksheets, Excel
crashes. Actually, it gives me the following:

"Run-time error '-21474147848 (80010108)':

Automation error
The object invoked has disconnected from its clients


And only gives me the option of End (not Continue or Debug - those are
greyed out). When I End, it seems to end (I can still work with the VBA
code), but when I go to the worksheet itself, I can't do anything.


Since I managed to narrow it down to when it hits worksheet #360, I tried
ending the automatic process when it hits 359, and then manually copying the
template worksheet. When I did that, it crashed Excel.


I'm on a 1.7GHz P4 Win 2000 (pro) box, with 512MB of memory. When saved to
disk (in Excel XP/2002 format), the file is 15MB in size (saved with just
over 300 worksheets). When I check the Windows Task Manager, even with 359
worksheets (at the crash), it indicates that Excel is only using 101MB of
memory.

So I doubt that it's an out-of-memory issue. But I could be wrong.


Either way, I'm in a major bind. If I can figure out that it's a memory
problem, I might be able to get more memory for this computer. But if it's
not, I'm at a loss to figure out what to try next.



Any ideas?

I'm rather stuck right now, as this is a project that needs to be done in
the next few weeks, so re-writing it in another platform (non-Excel) isn't
exactly an option. Unfortunately, limiting how many worksheets isn't exactly
an option either.

 

[Scott Lyon]

No, it's not the sheet names, as I've been careful to rename each one
(changing the sheet name itself as well as the name displayed on the "tab"
at the bottom of the workbook).


I'll check the Temp and Internet Temp folders. I usually keep my Recycle Bin
fairly cleaned out, but I don't check out those two folders too much at all.


I'll give that a try, and see if I have better luck.


Thanks!
-Scott

 

[Scott Lyon]

Well, I got my C:\WINNT\Temp folder chopped down to under 400 files, and the
C:\WINNT\Temp\Temporary Internet Files chopped down to under 100.
Unfortunately, I'm still hitting a brick wall when I try to add more
worksheets past that "360 worksheet" limit I seem to have discovered.


Any other ideas? Is it possible that this is a memory issue (although I have
512MB in this machine, and when saved the spreadsheet in question is under
20M, and even in memory Excel only takes just over 100MB when the
spreadsheet is loaded)?


Thanks!
-Scott