Clearing Memory

[Pete-Peters@sbcglobal.net]

I wrote a maco that will process 14 different spreadsheets. It will
automaticlly bring in a spreadsheet process it, write it out, and get
the next one to process. These are very big spreadsheets like over
30,000 rows each. The first one takes about 25 mins to process then
the next an hour until after about 5 times its up to 3 to 3 1/2 hours.
Is there a way to clear memory between each loop.
Thanks

[NickHK]

Pete,
If 25 minutes is the minimum process time for a WS, as a quick fix, you
could start a new instance of Excel, process the file, close Excel, open
Excel, process...
The added 30 seconds to close/reopen Excel would not add much to 25 minutes
and you would be certain to clear all memory.

NickHK

<Pete-Peters@sbcglobal.net> wrote in message
news:1142458760.866643.179160@u72g2000cwu.googlegroups.com...
> I wrote a maco that will process 14 different spreadsheets. It will
> automaticlly bring in a spreadsheet process it, write it out, and get
> the next one to process. These are very big spreadsheets like over
> 30,000 rows each. The first one takes about 25 mins to process then
> the next an hour until after about 5 times its up to 3 to 3 1/2 hours.
> Is there a way to clear memory between each loop.
> Thanks
>

[gimme_this_gimme_that@yahoo.com]

Could you tell us more about what your program does?

1. Does it create/save/close each workbook one-at-a-time or does it
save all workbooks at once?

2. Does creating a workbook fetch data from a database and format it?
If so,
do you do all the queries once up-front or do you do a new query each
time you create a workbook.

3. Are you certain the stalling issue has to do with memory and not
with an orphaned database connection? (Orphaned database connections
are a certain way to stall a program.)

4. Does each row require special formatting when it's written ? Do you
make the formatting changes a line-at-a-time or all at once?

5. If you're running a 700 Mhz machine (or faster) with a Gig of RAM
(or more) then the problem probably doesn't have to do with memory
allocation. 30,000 is big but not 3 to 3 1/2 hours big. It should take
less than 45 minutes.

How fast is your machine and what is your OS?

If I had to guess the issue has to do with orphaned DB connections.

[Pete-Peters@sbcglobal.net]

1. White each loop I'm creating/saving/and closing other excel
workbooks at different times within a loop.
2. I'm not getting any data from a database but only other excel
workbooks.
3. ?
4. I make formating changes on a column all at one time.
5. I have 2003 excel and 1Gig of ram. I did have 1/2 Gig but then I
ran out of memory that give me an error. Now it only takes long.

This macro is very big. I'm doing a lot. I only have a customer
number, item, and price ect... the marco will open a diffeent excel
workbook with customer infomation. I will use a Vlookup to find other
data then close that workbook. Then I open another excel workbook that
contains rebate data. this is like 87MM big. I will do concatenating
and vlookups to get the rebates for each line the close that excel
workbook when done with that step. I then use linked sheets to figue
EBITDA dollars (cost). I then run through a loop that will figure
EBITDA dollars for each contract on a weighed avg. when I get this
information I write out a new excel workbook with all this data. The
macro then will go get another district (start over) to process the
same way. each one taking longer and longer. This is just a quick
summary of what it does.

[Pete-Peters@sbcglobal.net]

Thanks Nick; but this is something I run at night after I go home.

[NickHK]

Pete,
Yes, so you automate the open/close of Excel from your initial instance of
Excel.
No user intervention.

It would of course be better to trace the root course, but this will at
least tell you if memory/not releasing references is to blame.

NickHK

<Pete-Peters@sbcglobal.net> wrote in message
news:1142518908.472956.323720@z34g2000cwc.googlegroups.com...
> Thanks Nick; but this is something I run at night after I go home.
>