Memory problem or Excel limitation

Hi,
I built an application that fills the sheets of a template with specific
data. So far, all is well. Then, I was asked if it would be possible to
repeat the operation for hundreds or thousands of workbooks. No problem, I
said. I then built another application based on the first one. It runs very
well an does its job perfectly. But Excel stalls after a specific number of
save operations. If the disk were full, I would understand, but here, there
is no reason.
I read all the memory problems on this workgroup and found a lot of good
comments. I tried all I could but things get worse as I modify the
application. It takes a little more time everytime I start the job, ie, a few
seconds. A reboot will not change a thing. It seems that something is hidden
inside Excel's temporary settings or working environment.
The main loop in the program opens a template, fills some sheets with
filtered data, creates a folder from the contract name and then saves the
Excel workbook in that folder. After 2797 save operations, Excel stops
operating (and the disk is not full).
Can someone help?
Note: I use Excel XP and Windows 2000.
Thanx
--
Jac Tremblay

Hello Jac,

As you call up the workbooks and save them Excel creates temporary files and saves them in the C:\Windows\Temp. This folder is not cleaned by rebooting. As these files accumulate, they use up both internal memory and disk space. The files can reach a point, as they have in your case, that the system slows down and stops responding. Check this folder and clear it. It's a very annoying "feature" of Windows.

Hope this helps,
Leith Ross

Jac

Can you post the code?

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Jac Tremblay wrote:
> Hi,
> I built an application that fills the sheets of a template with
> specific data. So far, all is well. Then, I was asked if it would be
> possible to repeat the operation for hundreds or thousands of
> workbooks. No problem, I said. I then built another application based
> on the first one. It runs very well an does its job perfectly. But
> Excel stalls after a specific number of save operations. If the disk
> were full, I would understand, but here, there is no reason.
> I read all the memory problems on this workgroup and found a lot of
> good comments. I tried all I could but things get worse as I modify
> the application. It takes a little more time everytime I start the
> job, ie, a few seconds. A reboot will not change a thing. It seems
> that something is hidden inside Excel's temporary settings or working
> environment.
> The main loop in the program opens a template, fills some sheets with
> filtered data, creates a folder from the contract name and then saves
> the Excel workbook in that folder. After 2797 save operations, Excel
> stops operating (and the disk is not full).
> Can someone help?
> Note: I use Excel XP and Windows 2000.
> Thanx

Hi ****,
I'm glad someone like you cares about my problem.
I sure can post the code, but there are some problems:
1- It fills exactly 32 pages which includes the code for ThisWorkbook, a
userform with a lot of GetActiveWindow and a worksheet.
2- It is filled with comments in french to help the maintenance team and all
the variables names are in french.
3- The application requires many files as input and different templates with
specific characteristics to work.
4- The application is built for french and english users so a lot of code
goes for messages, labels and other things like that.
What I will do is build a simple application that does about the same thing
and test it to see if the problem is always present. Then, I will complete my
post.
Thank you for your concern.

Jac Tremblay


"**** Kusleika" wrote:

> Jac
>
> Can you post the code?
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jac Tremblay wrote:
> > Hi,
> > I built an application that fills the sheets of a template with
> > specific data. So far, all is well. Then, I was asked if it would be
> > possible to repeat the operation for hundreds or thousands of
> > workbooks. No problem, I said. I then built another application based
> > on the first one. It runs very well an does its job perfectly. But
> > Excel stalls after a specific number of save operations. If the disk
> > were full, I would understand, but here, there is no reason.
> > I read all the memory problems on this workgroup and found a lot of
> > good comments. I tried all I could but things get worse as I modify
> > the application. It takes a little more time everytime I start the
> > job, ie, a few seconds. A reboot will not change a thing. It seems
> > that something is hidden inside Excel's temporary settings or working
> > environment.
> > The main loop in the program opens a template, fills some sheets with
> > filtered data, creates a folder from the contract name and then saves
> > the Excel workbook in that folder. After 2797 save operations, Excel
> > stops operating (and the disk is not full).
> > Can someone help?
> > Note: I use Excel XP and Windows 2000.
> > Thanx
>
>
>

Hi ****,
As I said in the other post answer, I wrote a little program that does some
of the job and the problem does not exists anymore. The code is down below. I
use a workbook with 3 sheets. The 1st one is filled with numbers in 26
columns * 1000 rows. I then fill the 2 other sheets with similar data. I
tried to create 6000 folders in which I saved the 6000 workbooks. No problem.
2 seconds for each operation, some 3 1/2 hours total time.
I suppose that the problem I get with the other application is a matter of
programming, memory leak or something like this. I tried to correct it
according to posts about memory but nothing changed. I guess I'll have to
live with it.
Here is the code I wrote. If there are some better ways to do things,
please, let me know.
Thank you for your time.

Option Explicit

'*******************************************************************
Sub SaveTonsOfWorkbooks()

' Jac Tremblay 2005-03-16

Dim lngNbXl As Long
Const strcXlTemplate As String = "Save Excel Workbooks test.xls"
Const strcPathToSaveTo As String = "C:\Temp\"
Dim strPathAndWbk As String
Const strcFolder As String = "Folder "
Const strcWorkbook As String = "Wbk "
Const strc0000 As String = "0000"
Dim lngI As Long
Dim lngJ As Long
Dim strFolderName As String
Dim strWbkName As String

Dim lngStart As Double
Dim lngEnd As Double
Dim lngTime As Double
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

' Ask for the number of iterations to do (workbooks to save).
lngNbXl = InputBox("Number of workbooks to create", "Sofica", "100")

lngI = 0
Application.ScreenUpdating = False
lngStart = Timer

For lngJ = 1 To lngNbXl

lngI = lngI + 1
Application.StatusBar = "Creating workbook " & lngI & " of " & lngNbXl

' Open the template to fill.
strPathAndWbk = strcPathToSaveTo & strcXlTemplate
Workbooks.Open strPathAndWbk

' Fill the sheets.
FillTheSheets

' Calculate the folder and workbook names.
strFolderName = strcFolder & Mid(strc0000, 1, 4 - Len(CStr(lngI))) &
lngI
strWbkName = strcWorkbook & Mid(strc0000, 1, 4 - Len(CStr(lngI))) & lngI

' Change the save folder.
ChDir strcPathToSaveTo

' Create a new folder.
On Error Resume Next
MkDir strFolderName

' Save the filled workbook and close it.
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFolderName & "\" & strWbkName &
".xls"
Application.DisplayAlerts = True
ActiveWorkbook.Close

Next lngJ

lngEnd = Timer
lngTime = lngEnd - lngStart
lngHour = lngTime \ 3600
If lngHour = 0 Then
lngMinute = lngTime \ 60
Else
lngMinute = (lngTime - lngHour * 3600) \ 60
End If
lngSecond = lngTime Mod 60

' Display a summary message.
MsgBox "Number of folders and workbooks created: " & lngI & _
vbNewLine & vbNewLine & _
"Time elapsed: " & lngHour & " hour(s), " & _
lngMinute & " minute(s) and " & _
lngSecond & " seconds."

Application.ScreenUpdating = True
Application.StatusBar = "Job done"

End Sub

'*******************************************************************
Sub FillTheSheets()

' Jac Tremblay 2005-03-16

' Insert data to make the workbook a little bigger.
Dim lngK As Long
Dim shtCopy As Worksheet
For lngK = 2 To 3
Set shtCopy = Sheets(lngK)
With shtCopy.Range("A1:Z200")
.FormulaR1C1 = "=ROW()*COLUMN()"
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Set shtCopy = Nothing
Next lngK
End Sub

'*******************************************************************

"**** Kusleika" wrote:

> Jac
>
> Can you post the code?
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jac Tremblay wrote:
> > Hi,
> > I built an application that fills the sheets of a template with
> > specific data. So far, all is well. Then, I was asked if it would be
> > possible to repeat the operation for hundreds or thousands of
> > workbooks. No problem, I said. I then built another application based
> > on the first one. It runs very well an does its job perfectly. But
> > Excel stalls after a specific number of save operations. If the disk
> > were full, I would understand, but here, there is no reason.
> > I read all the memory problems on this workgroup and found a lot of
> > good comments. I tried all I could but things get worse as I modify
> > the application. It takes a little more time everytime I start the
> > job, ie, a few seconds. A reboot will not change a thing. It seems
> > that something is hidden inside Excel's temporary settings or working
> > environment.
> > The main loop in the program opens a template, fills some sheets with
> > filtered data, creates a folder from the contract name and then saves
> > the Excel workbook in that folder. After 2797 save operations, Excel
> > stops operating (and the disk is not full).
> > Can someone help?
> > Note: I use Excel XP and Windows 2000.
> > Thanx
>
>
>

Jac

I don't see anything obvious at first glance. I'll mess around with it this
weekend and see if I can really break some stuff.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Jac Tremblay wrote:
> Hi ****,
> As I said in the other post answer, I wrote a little program that
> does some of the job and the problem does not exists anymore. The
> code is down below. I use a workbook with 3 sheets. The 1st one is
> filled with numbers in 26 columns * 1000 rows. I then fill the 2
> other sheets with similar data. I tried to create 6000 folders in
> which I saved the 6000 workbooks. No problem. 2 seconds for each
> operation, some 3 1/2 hours total time.
> I suppose that the problem I get with the other application is a
> matter of programming, memory leak or something like this. I tried to
> correct it according to posts about memory but nothing changed. I
> guess I'll have to live with it.
> Here is the code I wrote. If there are some better ways to do things,
> please, let me know.
> Thank you for your time.
>
> Option Explicit
>
> '*******************************************************************
> Sub SaveTonsOfWorkbooks()
>
> ' Jac Tremblay 2005-03-16
>
> Dim lngNbXl As Long
> Const strcXlTemplate As String = "Save Excel Workbooks test.xls"
> Const strcPathToSaveTo As String = "C:\Temp\"
> Dim strPathAndWbk As String
> Const strcFolder As String = "Folder "
> Const strcWorkbook As String = "Wbk "
> Const strc0000 As String = "0000"
> Dim lngI As Long
> Dim lngJ As Long
> Dim strFolderName As String
> Dim strWbkName As String
>
> Dim lngStart As Double
> Dim lngEnd As Double
> Dim lngTime As Double
> Dim lngHour As Long
> Dim lngMinute As Long
> Dim lngSecond As Long
>
> ' Ask for the number of iterations to do (workbooks to save).
> lngNbXl = InputBox("Number of workbooks to create", "Sofica", "100")
>
> lngI = 0
> Application.ScreenUpdating = False
> lngStart = Timer
>
> For lngJ = 1 To lngNbXl
>
> lngI = lngI + 1
> Application.StatusBar = "Creating workbook " & lngI & " of " &
> lngNbXl
>
> ' Open the template to fill.
> strPathAndWbk = strcPathToSaveTo & strcXlTemplate
> Workbooks.Open strPathAndWbk
>
> ' Fill the sheets.
> FillTheSheets
>
> ' Calculate the folder and workbook names.
> strFolderName = strcFolder & Mid(strc0000, 1, 4 -
> Len(CStr(lngI))) & lngI
> strWbkName = strcWorkbook & Mid(strc0000, 1, 4 -
> Len(CStr(lngI))) & lngI
>
> ' Change the save folder.
> ChDir strcPathToSaveTo
>
> ' Create a new folder.
> On Error Resume Next
> MkDir strFolderName
>
> ' Save the filled workbook and close it.
> Application.DisplayAlerts = False
> ActiveWorkbook.SaveAs Filename:=strFolderName & "\" & strWbkName
> & ".xls"
> Application.DisplayAlerts = True
> ActiveWorkbook.Close
>
> Next lngJ
>
> lngEnd = Timer
> lngTime = lngEnd - lngStart
> lngHour = lngTime \ 3600
> If lngHour = 0 Then
> lngMinute = lngTime \ 60
> Else
> lngMinute = (lngTime - lngHour * 3600) \ 60
> End If
> lngSecond = lngTime Mod 60
>
> ' Display a summary message.
> MsgBox "Number of folders and workbooks created: " & lngI & _
> vbNewLine & vbNewLine & _
> "Time elapsed: " & lngHour & " hour(s), " & _
> lngMinute & " minute(s) and " & _
> lngSecond & " seconds."
>
> Application.ScreenUpdating = True
> Application.StatusBar = "Job done"
>
> End Sub
>
> '*******************************************************************
> Sub FillTheSheets()
>
> ' Jac Tremblay 2005-03-16
>
> ' Insert data to make the workbook a little bigger.
> Dim lngK As Long
> Dim shtCopy As Worksheet
> For lngK = 2 To 3
> Set shtCopy = Sheets(lngK)
> With shtCopy.Range("A1:Z200")
> .FormulaR1C1 = "=ROW()*COLUMN()"
> .Copy
> .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> End With
> Application.CutCopyMode = False
> Set shtCopy = Nothing
> Next lngK
> End Sub
>
> '*******************************************************************
>
> "**** Kusleika" wrote:
>
>> Jac
>>
>> Can you post the code?
>>
>> --
>> **** Kusleika
>> Excel MVP
>> Daily Dose of Excel
>> www.*****-blog.com
>>
>> Jac Tremblay wrote:
>>> Hi,
>>> I built an application that fills the sheets of a template with
>>> specific data. So far, all is well. Then, I was asked if it would be
>>> possible to repeat the operation for hundreds or thousands of
>>> workbooks. No problem, I said. I then built another application
>>> based on the first one. It runs very well an does its job
>>> perfectly. But Excel stalls after a specific number of save
>>> operations. If the disk were full, I would understand, but here,
>>> there is no reason.
>>> I read all the memory problems on this workgroup and found a lot of
>>> good comments. I tried all I could but things get worse as I modify
>>> the application. It takes a little more time everytime I start the
>>> job, ie, a few seconds. A reboot will not change a thing. It seems
>>> that something is hidden inside Excel's temporary settings or
>>> working environment.
>>> The main loop in the program opens a template, fills some sheets
>>> with filtered data, creates a folder from the contract name and
>>> then saves the Excel workbook in that folder. After 2797 save
>>> operations, Excel stops operating (and the disk is not full).
>>> Can someone help?
>>> Note: I use Excel XP and Windows 2000.
>>> Thanx

Hi -

In your other code, do you call any sort of add-in?

We've had problems with add-ins that call external DLLs and Excel's
garbage collection with large procedures that repeat many thousands of
times, in our case creating charts.

The problems are very gnarly and we can't solve them: our routines are
written in VB6 in an external DLL that runs clean each and every time
it executes, but run it several thousand times from within Excel and it
slowly eats up all the available handles within Excel.

Sort of neat to watch. We sometimes take bets to see when it completely
hoses the machine. Then again, we're easily amused...

John

Hi John,
No, the code does not call add-ins or external DLLs. But it does a lot of
copy and paste operations between different worksheets. The main loop reads a
text file to determine the language to use as a template; there is a French
and an English template. Two text files are imported in two worksheets and
the main job is based on those informations. The first contains the contract
numbers and the second contains the details of each contract. This second
worksheet must be filtered on the contract number before its data can be
copied into the selected template. The resulting workbook is saved in a newly
created folder. That is all.
In the initialization process, I use FileSystemObjects to validate the
folders in the userform provided and each of them is set to nothing as soon
as they are not needed anymore.
Of course, there are too many Select and Activate but i could not eliminate
them all.
I really cannot figure out what the problem is. But I will keep looking for
a solution.
Hoping we'll find a solution...
Thanks

"[email protected]" wrote:

> Hi -
>
> In your other code, do you call any sort of add-in?
>
> We've had problems with add-ins that call external DLLs and Excel's
> garbage collection with large procedures that repeat many thousands of
> times, in our case creating charts.
>
> The problems are very gnarly and we can't solve them: our routines are
> written in VB6 in an external DLL that runs clean each and every time
> it executes, but run it several thousand times from within Excel and it
> slowly eats up all the available handles within Excel.
>
> Sort of neat to watch. We sometimes take bets to see when it completely
> hoses the machine. Then again, we're easily amused...
>
> John
>
>

Hi ****,
I think that you should look seriously into that code because there is a
real problem. It does not seem to be in the code though. It seems to be a
real Excel limitation, to me anyway. Here is the problem:
When you run that code for, lets say 100 files, it takes about 2 minutes (if
you deactivate Norton temporarily). If you run it a second time, it will take
a few seconds more, 10 or 15. And it will take longer and longer every time
you run it even if you reboot your computer. That
is unreal. There must be a problem somewhere.
That code I wrote is only a test sample to explain my question on the
newsgroup. The real application covers 32 pages and does present the same
problem (there are over 6800 files to be created) . I sure wish I can find
answer to this strange behaviour.
If you or anyone else can help, I would appreciate.
Thanks.

"**** Kusleika" wrote:

> Jac
>
> I don't see anything obvious at first glance. I'll mess around with it this
> weekend and see if I can really break some stuff.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jac Tremblay wrote:
> > Hi ****,
> > As I said in the other post answer, I wrote a little program that
> > does some of the job and the problem does not exists anymore. The
> > code is down below. I use a workbook with 3 sheets. The 1st one is
> > filled with numbers in 26 columns * 1000 rows. I then fill the 2
> > other sheets with similar data. I tried to create 6000 folders in
> > which I saved the 6000 workbooks. No problem. 2 seconds for each
> > operation, some 3 1/2 hours total time.
> > I suppose that the problem I get with the other application is a
> > matter of programming, memory leak or something like this. I tried to
> > correct it according to posts about memory but nothing changed. I
> > guess I'll have to live with it.
> > Here is the code I wrote. If there are some better ways to do things,
> > please, let me know.
> > Thank you for your time.
> >
> > Option Explicit
> >
> > '*******************************************************************
> > Sub SaveTonsOfWorkbooks()
> >
> > ' Jac Tremblay 2005-03-16
> >
> > Dim lngNbXl As Long
> > Const strcXlTemplate As String = "Save Excel Workbooks test.xls"
> > Const strcPathToSaveTo As String = "C:\Temp\"
> > Dim strPathAndWbk As String
> > Const strcFolder As String = "Folder "
> > Const strcWorkbook As String = "Wbk "
> > Const strc0000 As String = "0000"
> > Dim lngI As Long
> > Dim lngJ As Long
> > Dim strFolderName As String
> > Dim strWbkName As String
> >
> > Dim lngStart As Double
> > Dim lngEnd As Double
> > Dim lngTime As Double
> > Dim lngHour As Long
> > Dim lngMinute As Long
> > Dim lngSecond As Long
> >
> > ' Ask for the number of iterations to do (workbooks to save).
> > lngNbXl = InputBox("Number of workbooks to create", "Sofica", "100")
> >
> > lngI = 0
> > Application.ScreenUpdating = False
> > lngStart = Timer
> >
> > For lngJ = 1 To lngNbXl
> >
> > lngI = lngI + 1
> > Application.StatusBar = "Creating workbook " & lngI & " of " &
> > lngNbXl
> >
> > ' Open the template to fill.
> > strPathAndWbk = strcPathToSaveTo & strcXlTemplate
> > Workbooks.Open strPathAndWbk
> >
> > ' Fill the sheets.
> > FillTheSheets
> >
> > ' Calculate the folder and workbook names.
> > strFolderName = strcFolder & Mid(strc0000, 1, 4 -
> > Len(CStr(lngI))) & lngI
> > strWbkName = strcWorkbook & Mid(strc0000, 1, 4 -
> > Len(CStr(lngI))) & lngI
> >
> > ' Change the save folder.
> > ChDir strcPathToSaveTo
> >
> > ' Create a new folder.
> > On Error Resume Next
> > MkDir strFolderName
> >
> > ' Save the filled workbook and close it.
> > Application.DisplayAlerts = False
> > ActiveWorkbook.SaveAs Filename:=strFolderName & "\" & strWbkName
> > & ".xls"
> > Application.DisplayAlerts = True
> > ActiveWorkbook.Close
> >
> > Next lngJ
> >
> > lngEnd = Timer
> > lngTime = lngEnd - lngStart
> > lngHour = lngTime \ 3600
> > If lngHour = 0 Then
> > lngMinute = lngTime \ 60
> > Else
> > lngMinute = (lngTime - lngHour * 3600) \ 60
> > End If
> > lngSecond = lngTime Mod 60
> >
> > ' Display a summary message.
> > MsgBox "Number of folders and workbooks created: " & lngI & _
> > vbNewLine & vbNewLine & _
> > "Time elapsed: " & lngHour & " hour(s), " & _
> > lngMinute & " minute(s) and " & _
> > lngSecond & " seconds."
> >
> > Application.ScreenUpdating = True
> > Application.StatusBar = "Job done"
> >
> > End Sub
> >
> > '*******************************************************************
> > Sub FillTheSheets()
> >
> > ' Jac Tremblay 2005-03-16
> >
> > ' Insert data to make the workbook a little bigger.
> > Dim lngK As Long
> > Dim shtCopy As Worksheet
> > For lngK = 2 To 3
> > Set shtCopy = Sheets(lngK)
> > With shtCopy.Range("A1:Z200")
> > .FormulaR1C1 = "=ROW()*COLUMN()"
> > .Copy
> > .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
> > End With
> > Application.CutCopyMode = False
> > Set shtCopy = Nothing
> > Next lngK
> > End Sub
> >
> > '*******************************************************************
> >
> > "**** Kusleika" wrote:
> >
> >> Jac
> >>
> >> Can you post the code?
> >>
> >> --
> >> **** Kusleika
> >> Excel MVP
> >> Daily Dose of Excel
> >> www.*****-blog.com
> >>
> >> Jac Tremblay wrote:
> >>> Hi,
> >>> I built an application that fills the sheets of a template with
> >>> specific data. So far, all is well. Then, I was asked if it would be
> >>> possible to repeat the operation for hundreds or thousands of
> >>> workbooks. No problem, I said. I then built another application
> >>> based on the first one. It runs very well an does its job
> >>> perfectly. But Excel stalls after a specific number of save
> >>> operations. If the disk were full, I would understand, but here,
> >>> there is no reason.
> >>> I read all the memory problems on this workgroup and found a lot of
> >>> good comments. I tried all I could but things get worse as I modify
> >>> the application. It takes a little more time everytime I start the
> >>> job, ie, a few seconds. A reboot will not change a thing. It seems
> >>> that something is hidden inside Excel's temporary settings or
> >>> working environment.
> >>> The main loop in the program opens a template, fills some sheets
> >>> with filtered data, creates a folder from the contract name and
> >>> then saves the Excel workbook in that folder. After 2797 save
> >>> operations, Excel stops operating (and the disk is not full).
> >>> Can someone help?
> >>> Note: I use Excel XP and Windows 2000.
> >>> Thanx
>
>
>

Hi ****,
I wander if you got my last comment about the OP. There IS a problem with
the application I wrote and it does not seem to be in the code (not exactly
sure about that, though). When the macro is run the first time, it takes 2
minutes; the other times it is run, it takes some 10 to 15 seconds more each
time. There is no apparent reason for that. Have you tried to do that? Do you
have an idea what the problem could be?

Thank you

"**** Kusleika" wrote:

> Jac
>
> Can you post the code?
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jac Tremblay wrote:
> > Hi,
> > I built an application that fills the sheets of a template with
> > specific data. So far, all is well. Then, I was asked if it would be
> > possible to repeat the operation for hundreds or thousands of
> > workbooks. No problem, I said. I then built another application based
> > on the first one. It runs very well an does its job perfectly. But
> > Excel stalls after a specific number of save operations. If the disk
> > were full, I would understand, but here, there is no reason.
> > I read all the memory problems on this workgroup and found a lot of
> > good comments. I tried all I could but things get worse as I modify
> > the application. It takes a little more time everytime I start the
> > job, ie, a few seconds. A reboot will not change a thing. It seems
> > that something is hidden inside Excel's temporary settings or working
> > environment.
> > The main loop in the program opens a template, fills some sheets with
> > filtered data, creates a folder from the contract name and then saves
> > the Excel workbook in that folder. After 2797 save operations, Excel
> > stops operating (and the disk is not full).
> > Can someone help?
> > Note: I use Excel XP and Windows 2000.
> > Thanx
>
>
>

Jac

Sorry I couldn't get back to you quicker. As you've discovered, there's
nothing in the code that's causing the problem. Just what the problem is
though is going to be difficult to predict. I'm pretty sure that Excel is
not doing some clean up that it should do after it interacts with Windows,
namely opening and saving the files. I have no idea what that would be,
however.

If you ask Microsoft, I know what they'll tell you: Don't do that with
Excel. It's not set up to do 6,000 file operations. And they're right.
You just shouldn't be doing that much - although I can appreciate that you
want to.

You need to redesign your code to limit the number of file operations. Not
an easy task, I'm sure. If you want to describe your situation in more
detail, I (or someone) may be able to give you some creative suggestions on
how you might achieve your goals. Until there, here are some things that
you might want to try:
Open the template file once and use SaveCopyAs or SaveAs to create the
files. You'll still have 6,000 save operations but you'll have 5,999 less
open operations which may be enough to make a difference.
Create CSV files using VBA's file operation commands instead of Excel's open
and save methods. I don't know if this will make a difference, but it may
be worth a try. CSV's will open natively in Excel, though they obviously
won't be the more familiar BIFF format. I doubt the consumers of those
6,000 files care about that, though.
Perhaps you could use ADO and an INSERT INTO sql statement to populate the
workbooks. You may be able to get away with not even opening them or saving
them. Who knows at what cost, though.

I'm sorry I didn't have the chance to test this more thoroughly. I've just
been too busy, but I do find it to be an intriguing situation.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Jac Tremblay wrote:
> Hi ****,
> I think that you should look seriously into that code because there
> is a real problem. It does not seem to be in the code though. It
> seems to be a real Excel limitation, to me anyway. Here is the
> problem:
> When you run that code for, lets say 100 files, it takes about 2
> minutes (if you deactivate Norton temporarily). If you run it a
> second time, it will take a few seconds more, 10 or 15. And it will
> take longer and longer every time you run it even if you reboot your
> computer. That
> is unreal. There must be a problem somewhere.
> That code I wrote is only a test sample to explain my question on the
> newsgroup. The real application covers 32 pages and does present the
> same problem (there are over 6800 files to be created) . I sure wish
> I can find answer to this strange behaviour.
> If you or anyone else can help, I would appreciate.
> Thanks.
>
> "**** Kusleika" wrote:
>
>> Jac
>>
>> I don't see anything obvious at first glance. I'll mess around with
>> it this weekend and see if I can really break some stuff.
>>
>> --
>> **** Kusleika
>> Excel MVP
>> Daily Dose of Excel
>> www.*****-blog.com
>>
>> Jac Tremblay wrote:
>>> Hi ****,
>>> As I said in the other post answer, I wrote a little program that
>>> does some of the job and the problem does not exists anymore. The
>>> code is down below. I use a workbook with 3 sheets. The 1st one is
>>> filled with numbers in 26 columns * 1000 rows. I then fill the 2
>>> other sheets with similar data. I tried to create 6000 folders in
>>> which I saved the 6000 workbooks. No problem. 2 seconds for each
>>> operation, some 3 1/2 hours total time.
>>> I suppose that the problem I get with the other application is a
>>> matter of programming, memory leak or something like this. I tried
>>> to correct it according to posts about memory but nothing changed. I
>>> guess I'll have to live with it.
>>> Here is the code I wrote. If there are some better ways to do
>>> things, please, let me know.
>>> Thank you for your time.
>>>
>>> Option Explicit
>>>
>>> '*******************************************************************
>>> Sub SaveTonsOfWorkbooks()
>>>
>>> ' Jac Tremblay 2005-03-16
>>>
>>> Dim lngNbXl As Long
>>> Const strcXlTemplate As String = "Save Excel Workbooks test.xls"
>>> Const strcPathToSaveTo As String = "C:\Temp\"
>>> Dim strPathAndWbk As String
>>> Const strcFolder As String = "Folder "
>>> Const strcWorkbook As String = "Wbk "
>>> Const strc0000 As String = "0000"
>>> Dim lngI As Long
>>> Dim lngJ As Long
>>> Dim strFolderName As String
>>> Dim strWbkName As String
>>>
>>> Dim lngStart As Double
>>> Dim lngEnd As Double
>>> Dim lngTime As Double
>>> Dim lngHour As Long
>>> Dim lngMinute As Long
>>> Dim lngSecond As Long
>>>
>>> ' Ask for the number of iterations to do (workbooks to save).
>>> lngNbXl = InputBox("Number of workbooks to create", "Sofica",
>>> "100")
>>>
>>> lngI = 0
>>> Application.ScreenUpdating = False
>>> lngStart = Timer
>>>
>>> For lngJ = 1 To lngNbXl
>>>
>>> lngI = lngI + 1
>>> Application.StatusBar = "Creating workbook " & lngI & " of " &
>>> lngNbXl
>>>
>>> ' Open the template to fill.
>>> strPathAndWbk = strcPathToSaveTo & strcXlTemplate
>>> Workbooks.Open strPathAndWbk
>>>
>>> ' Fill the sheets.
>>> FillTheSheets
>>>
>>> ' Calculate the folder and workbook names.
>>> strFolderName = strcFolder & Mid(strc0000, 1, 4 -
>>> Len(CStr(lngI))) & lngI
>>> strWbkName = strcWorkbook & Mid(strc0000, 1, 4 -
>>> Len(CStr(lngI))) & lngI
>>>
>>> ' Change the save folder.
>>> ChDir strcPathToSaveTo
>>>
>>> ' Create a new folder.
>>> On Error Resume Next
>>> MkDir strFolderName
>>>
>>> ' Save the filled workbook and close it.
>>> Application.DisplayAlerts = False
>>> ActiveWorkbook.SaveAs Filename:=strFolderName & "\" &
>>> strWbkName & ".xls"
>>> Application.DisplayAlerts = True
>>> ActiveWorkbook.Close
>>>
>>> Next lngJ
>>>
>>> lngEnd = Timer
>>> lngTime = lngEnd - lngStart
>>> lngHour = lngTime \ 3600
>>> If lngHour = 0 Then
>>> lngMinute = lngTime \ 60
>>> Else
>>> lngMinute = (lngTime - lngHour * 3600) \ 60
>>> End If
>>> lngSecond = lngTime Mod 60
>>>
>>> ' Display a summary message.
>>> MsgBox "Number of folders and workbooks created: " & lngI & _
>>> vbNewLine & vbNewLine & _
>>> "Time elapsed: " & lngHour & " hour(s), " & _
>>> lngMinute & " minute(s) and " & _
>>> lngSecond & " seconds."
>>>
>>> Application.ScreenUpdating = True
>>> Application.StatusBar = "Job done"
>>>
>>> End Sub
>>>
>>> '*******************************************************************
>>> Sub FillTheSheets()
>>>
>>> ' Jac Tremblay 2005-03-16
>>>
>>> ' Insert data to make the workbook a little bigger.
>>> Dim lngK As Long
>>> Dim shtCopy As Worksheet
>>> For lngK = 2 To 3
>>> Set shtCopy = Sheets(lngK)
>>> With shtCopy.Range("A1:Z200")
>>> .FormulaR1C1 = "=ROW()*COLUMN()"
>>> .Copy
>>> .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
>>> SkipBlanks:=False, Transpose:=False
>>> End With
>>> Application.CutCopyMode = False
>>> Set shtCopy = Nothing
>>> Next lngK
>>> End Sub
>>>
>>> '*******************************************************************
>>>
>>> "**** Kusleika" wrote:
>>>
>>>> Jac
>>>>
>>>> Can you post the code?
>>>>
>>>> --
>>>> **** Kusleika
>>>> Excel MVP
>>>> Daily Dose of Excel
>>>> www.*****-blog.com
>>>>
>>>> Jac Tremblay wrote:
>>>>> Hi,
>>>>> I built an application that fills the sheets of a template with
>>>>> specific data. So far, all is well. Then, I was asked if it would
>>>>> be possible to repeat the operation for hundreds or thousands of
>>>>> workbooks. No problem, I said. I then built another application
>>>>> based on the first one. It runs very well an does its job
>>>>> perfectly. But Excel stalls after a specific number of save
>>>>> operations. If the disk were full, I would understand, but here,
>>>>> there is no reason.
>>>>> I read all the memory problems on this workgroup and found a lot
>>>>> of good comments. I tried all I could but things get worse as I
>>>>> modify the application. It takes a little more time everytime I
>>>>> start the job, ie, a few seconds. A reboot will not change a
>>>>> thing. It seems that something is hidden inside Excel's temporary
>>>>> settings or working environment.
>>>>> The main loop in the program opens a template, fills some sheets
>>>>> with filtered data, creates a folder from the contract name and
>>>>> then saves the Excel workbook in that folder. After 2797 save
>>>>> operations, Excel stops operating (and the disk is not full).
>>>>> Can someone help?
>>>>> Note: I use Excel XP and Windows 2000.
>>>>> Thanx

Hi ****,
Your post is interesting and I thank you for your time.
I think I should explain why I want to do all those operations with Excel.
It is a long story that I will shorten a bit.
There is a big system on a mainframe computer that deals with group
insurance policies. Some additional data have been stored and edited
temporarily in Excel worbooks. At the time, it was easier to do so because a
change in the central system was too costly (and maybe not wanted without
some other changes) and the number of contracts was low. As time went on,
that number augmented drastically, specially the ones they started getting
from the Net.
As the manual work got too heavy, they asked me to help them automate some
tasks so they could continue working that way for some time, until the new
system is ready (by next year, I guess).
So far so good. One application I developped allowed the user to fill a
template with thousands of lines of data from text files extracted from the
central via a Unix platform. Another one allowed the user to create the text
files from a workbook so they could be loaded back into the central system.
Now that they realize the savings that those applications permitted, they
want to go farther and automate the loading of all the workbooks in one
operation (maybe divided into smaller chunks). And that is where I am. The
system is now delivered and my job is done but the performance is not
interesting and is getting worse as the tests go on.
Today, they found out that the files extracted from the central were not the
right format. After correcting the problem, the performance returned to what
it was in the beginning and that is acceptable now. So we will leave it that
way for now.
It remains that Excel does pose a problem when one needs to create many
folders and save many files, particularly when those numbers reach the
thousands.
I will keep on looking for a solution.
Thanks again, ****, for your time. I appreciate.

"**** Kusleika" wrote:

> Jac
>
> Sorry I couldn't get back to you quicker. As you've discovered, there's
> nothing in the code that's causing the problem. Just what the problem is
> though is going to be difficult to predict. I'm pretty sure that Excel is
> not doing some clean up that it should do after it interacts with Windows,
> namely opening and saving the files. I have no idea what that would be,
> however.
>
> If you ask Microsoft, I know what they'll tell you: Don't do that with
> Excel. It's not set up to do 6,000 file operations. And they're right.
> You just shouldn't be doing that much - although I can appreciate that you
> want to.
>
> You need to redesign your code to limit the number of file operations. Not
> an easy task, I'm sure. If you want to describe your situation in more
> detail, I (or someone) may be able to give you some creative suggestions on
> how you might achieve your goals. Until there, here are some things that
> you might want to try:
> Open the template file once and use SaveCopyAs or SaveAs to create the
> files. You'll still have 6,000 save operations but you'll have 5,999 less
> open operations which may be enough to make a difference.
> Create CSV files using VBA's file operation commands instead of Excel's open
> and save methods. I don't know if this will make a difference, but it may
> be worth a try. CSV's will open natively in Excel, though they obviously
> won't be the more familiar BIFF format. I doubt the consumers of those
> 6,000 files care about that, though.
> Perhaps you could use ADO and an INSERT INTO sql statement to populate the
> workbooks. You may be able to get away with not even opening them or saving
> them. Who knows at what cost, though.
>
> I'm sorry I didn't have the chance to test this more thoroughly. I've just
> been too busy, but I do find it to be an intriguing situation.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jac Tremblay wrote:
> > Hi ****,
> > I think that you should look seriously into that code because there
> > is a real problem. It does not seem to be in the code though. It
> > seems to be a real Excel limitation, to me anyway. Here is the
> > problem:
> > When you run that code for, lets say 100 files, it takes about 2
> > minutes (if you deactivate Norton temporarily). If you run it a
> > second time, it will take a few seconds more, 10 or 15. And it will
> > take longer and longer every time you run it even if you reboot your
> > computer. That
> > is unreal. There must be a problem somewhere.
> > That code I wrote is only a test sample to explain my question on the
> > newsgroup. The real application covers 32 pages and does present the
> > same problem (there are over 6800 files to be created) . I sure wish
> > I can find answer to this strange behaviour.
> > If you or anyone else can help, I would appreciate.
> > Thanks.
> >
> > "**** Kusleika" wrote:
> >
> >> Jac
> >>
> >> I don't see anything obvious at first glance. I'll mess around with
> >> it this weekend and see if I can really break some stuff.
> >>
> >> --
> >> **** Kusleika
> >> Excel MVP
> >> Daily Dose of Excel
> >> www.*****-blog.com
> >>
> >> Jac Tremblay wrote:
> >>> Hi ****,
> >>> As I said in the other post answer, I wrote a little program that
> >>> does some of the job and the problem does not exists anymore. The
> >>> code is down below. I use a workbook with 3 sheets. The 1st one is
> >>> filled with numbers in 26 columns * 1000 rows. I then fill the 2
> >>> other sheets with similar data. I tried to create 6000 folders in
> >>> which I saved the 6000 workbooks. No problem. 2 seconds for each
> >>> operation, some 3 1/2 hours total time.
> >>> I suppose that the problem I get with the other application is a
> >>> matter of programming, memory leak or something like this. I tried
> >>> to correct it according to posts about memory but nothing changed. I
> >>> guess I'll have to live with it.
> >>> Here is the code I wrote. If there are some better ways to do
> >>> things, please, let me know.
> >>> Thank you for your time.
> >>>
> >>> Option Explicit
> >>>
> >>> '*******************************************************************
> >>> Sub SaveTonsOfWorkbooks()
> >>>
> >>> ' Jac Tremblay 2005-03-16
> >>>
> >>> Dim lngNbXl As Long
> >>> Const strcXlTemplate As String = "Save Excel Workbooks test.xls"
> >>> Const strcPathToSaveTo As String = "C:\Temp\"
> >>> Dim strPathAndWbk As String
> >>> Const strcFolder As String = "Folder "
> >>> Const strcWorkbook As String = "Wbk "
> >>> Const strc0000 As String = "0000"
> >>> Dim lngI As Long
> >>> Dim lngJ As Long
> >>> Dim strFolderName As String
> >>> Dim strWbkName As String
> >>>
> >>> Dim lngStart As Double
> >>> Dim lngEnd As Double
> >>> Dim lngTime As Double
> >>> Dim lngHour As Long
> >>> Dim lngMinute As Long
> >>> Dim lngSecond As Long
> >>>
> >>> ' Ask for the number of iterations to do (workbooks to save).
> >>> lngNbXl = InputBox("Number of workbooks to create", "Sofica",
> >>> "100")
> >>>
> >>> lngI = 0
> >>> Application.ScreenUpdating = False
> >>> lngStart = Timer
> >>>
> >>> For lngJ = 1 To lngNbXl
> >>>
> >>> lngI = lngI + 1
> >>> Application.StatusBar = "Creating workbook " & lngI & " of " &
> >>> lngNbXl
> >>>
> >>> ' Open the template to fill.
> >>> strPathAndWbk = strcPathToSaveTo & strcXlTemplate
> >>> Workbooks.Open strPathAndWbk
> >>>
> >>> ' Fill the sheets.
> >>> FillTheSheets
> >>>
> >>> ' Calculate the folder and workbook names.
> >>> strFolderName = strcFolder & Mid(strc0000, 1, 4 -
> >>> Len(CStr(lngI))) & lngI
> >>> strWbkName = strcWorkbook & Mid(strc0000, 1, 4 -
> >>> Len(CStr(lngI))) & lngI
> >>>
> >>> ' Change the save folder.
> >>> ChDir strcPathToSaveTo
> >>>
> >>> ' Create a new folder.
> >>> On Error Resume Next
> >>> MkDir strFolderName
> >>>
> >>> ' Save the filled workbook and close it.
> >>> Application.DisplayAlerts = False
> >>> ActiveWorkbook.SaveAs Filename:=strFolderName & "\" &
> >>> strWbkName & ".xls"
> >>> Application.DisplayAlerts = True
> >>> ActiveWorkbook.Close
> >>>
> >>> Next lngJ
> >>>
> >>> lngEnd = Timer
> >>> lngTime = lngEnd - lngStart
> >>> lngHour = lngTime \ 3600
> >>> If lngHour = 0 Then
> >>> lngMinute = lngTime \ 60
> >>> Else
> >>> lngMinute = (lngTime - lngHour * 3600) \ 60
> >>> End If
> >>> lngSecond = lngTime Mod 60
> >>>
> >>> ' Display a summary message.
> >>> MsgBox "Number of folders and workbooks created: " & lngI & _
> >>> vbNewLine & vbNewLine & _
> >>> "Time elapsed: " & lngHour & " hour(s), " & _
> >>> lngMinute & " minute(s) and " & _
> >>> lngSecond & " seconds."
> >>>
> >>> Application.ScreenUpdating = True
> >>> Application.StatusBar = "Job done"
> >>>
> >>> End Sub
> >>>
> >>> '*******************************************************************
> >>> Sub FillTheSheets()
> >>>
> >>> ' Jac Tremblay 2005-03-16
> >>>
> >>> ' Insert data to make the workbook a little bigger.
> >>> Dim lngK As Long
> >>> Dim shtCopy As Worksheet
> >>> For lngK = 2 To 3
> >>> Set shtCopy = Sheets(lngK)
> >>> With shtCopy.Range("A1:Z200")
> >>> .FormulaR1C1 = "=ROW()*COLUMN()"
> >>> .Copy
> >>> .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> >>> SkipBlanks:=False, Transpose:=False
> >>> End With
> >>> Application.CutCopyMode = False
> >>> Set shtCopy = Nothing
> >>> Next lngK
> >>> End Sub
> >>>
> >>> '*******************************************************************
> >>>
> >>> "**** Kusleika" wrote:
> >>>
> >>>> Jac
> >>>>
> >>>> Can you post the code?
> >>>>
> >>>> --
> >>>> **** Kusleika
> >>>> Excel MVP
> >>>> Daily Dose of Excel
> >>>> www.*****-blog.com
> >>>>
> >>>> Jac Tremblay wrote:
> >>>>> Hi,
> >>>>> I built an application that fills the sheets of a template with
> >>>>> specific data. So far, all is well. Then, I was asked if it would
> >>>>> be possible to repeat the operation for hundreds or thousands of
> >>>>> workbooks. No problem, I said. I then built another application
> >>>>> based on the first one. It runs very well an does its job
> >>>>> perfectly. But Excel stalls after a specific number of save
> >>>>> operations. If the disk were full, I would understand, but here,
> >>>>> there is no reason.
> >>>>> I read all the memory problems on this workgroup and found a lot
> >>>>> of good comments. I tried all I could but things get worse as I
> >>>>> modify the application. It takes a little more time everytime I
> >>>>> start the job, ie, a few seconds. A reboot will not change a
> >>>>> thing. It seems that something is hidden inside Excel's temporary
> >>>>> settings or working environment.
> >>>>> The main loop in the program opens a template, fills some sheets
> >>>>> with filtered data, creates a folder from the contract name and
> >>>>> then saves the Excel workbook in that folder. After 2797 save
> >>>>> operations, Excel stops operating (and the disk is not full).
> >>>>> Can someone help?
> >>>>> Note: I use Excel XP and Windows 2000.
> >>>>> Thanx
>
>
>