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

Jac

Can you post the code?

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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 Dick,
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


"Dick Kusleika" wrote:

Jac

Can you post the code?

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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

" 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 Dick,
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

"Dick Kusleika" wrote:

Jac

Can you post the code?

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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