Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Memory
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Memory
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 wrote in message oups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Memory
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Memory
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Memory
Thanks Nick; but this is something I run at night after I go home.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Memory
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 wrote in message oups.com... Thanks Nick; but this is something I run at night after I go home. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
XL 2007 - Out of Memory - memory leak/bug? | Excel Discussion (Misc queries) | |||
clearing memory /clipboard | Excel Programming | |||
Clearing Memory | Excel Programming | |||
Clearing Memory agter an update | Excel Programming |