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

"steve" wrote in message
...
Scott,

If you go to the VB Editor and look at the Project Explore each worksheet
will look like Sheet1, Sheet2, Sheet2, etc.
If you copy Sheet1 you will find Sheet11.
Copy Sheet1 again and you will find Sheet12.

Now if you copy Sheet11 you will find Sheet111.
I can't remember how many of these you can make but when the string gets
too
long Excel screams (Sheet11111111111111111111111).

Don't know if this is your problem.

Another issue is the size of your Temp folder, Internet Temp folder, and
your RecyleBin. If they get too full they play havoc with Excel.
Especially the internet temp folder (I try to keep this under 200 files
but
have gotton up to over 5,000). Check these out.

steve

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in
message ...
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 Befo=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.