Excel Memory Problem-Resolved- i think

[Nimrod]

I have an excel macro that makes new workbooks,copies sheets into them and then closes the new workbooks.
This process has a large amount of copiing and formula calculations.
MY PROBLEM: Is that eventually the program freezes due to memory problems. Does anyone know how to clear the ScratchPad memory or anything else ... programatically .. that could help with this problem

Thanks for reading ... hope to hear from someone who can help

[ This Message was edited by: Nimrod on 2002-05-19 02:11 ]

[elgringo56]

Putting this in your macro will clear the clipboard

Application.CopyObjectsWithCells = False
Application.CutCopyMode = False

[Nimrod]

De agradecimiento mucho elgringo ... I will run a test right now .

[elgringo56]

ok, but this old gringo does not speak a to much spanish, just limp along on my spanglish. Hope it helps some, it did for me.

[Nimrod]

Sorry I haven't gotten back to you yet but the program is still running... takes about an hour for the process to run its course. IE. There are about 100 Workbooks that get made up ... each workbook has 4 to 12 worksheets ... and each sheet has at least 1200 formulas ... with data coming from 19 different DB's....
I will let you know if your suggestion did the trick or not
Again thanks for the help

[Nimrod]

%#^& ... No luck does anyone else have any ideas ?

[elgringo56]

Sorry, Nimrod, that was my best thought on it. Good luck with it

[Nimrod]

Thanks Elgringo , I do appreciate the effort. Hopefully I can return the favour some time.

[Brian]

1) If you can't find a true solution, then I sugest finding a method of accomodation by determining what size limits you need to stay below and then adjust the macro accordingly.

Therefore I suggest displaying information about the current status so that you can determine how many workbooks were open, or how many cells were just copied, or the like so that you can

To display info you can use;
Call StatusBarMsg(10, "Adding Rows to Sheet SIE.") 'Display Status in StatusBar:

(or keep updating a msgbox)

To get system info you can use the worksheet function: Info( )
"memavail" Amount of memory available, in bytes.
"memused" Amount of memory being used for data.
"numfile" Number of active worksheets.

2) To determine if memory really is the problem, try using a computer with more memory. Or decrease the memory in your compter to see if the problem happens sooner.

3) Remember to check your virtual memory and swap file sizes, and available disk space.

4) Have you tested your macro with smaller dummy files to confirm that it truely is a memory problem and not something else?

[Brian]

Some windows programs do not release all memory whem a file closes, resulting in less available memory the longer you work.

I don't know if Excel sufferes from this, but you could try closing excel and then repoening before running the macro, or even rebooting the coumputer.

I expect that you know this, but unload all unnecessary add-ins, close other programs that are running, turn off screen updataing, place calculation in manual mode, etc.

And this ad for a memory product just poped up while I was surfing another site: http://dl.winsite.com/bin/downl?win95/excel/ASAPUtilities_setup.exe|532|4000000037293