Tips on Optimizing Memory Use by Excel Books

[largeselection]

Quick Summary: Is it possible to simulate closing Excel and opening Excel from within a workbook using a Macro?

Some Details: Basically I have a workbook which runs and when it is done I see that the memory use (in the Ctrl+alt+del system tasks section) for Excel is at 1,250MB. It won't let me update a pivot table I have in the workbook because it says the system is out of resources. I noticed that if I close out of the Excel and re-open the file, Excel will only be at 270MB and everything works fine so it appears to be the result of the code that I'm running that causes the issue. So is it possible to simulate the closing of excel without actually closing excel?

More Details: The way the workbook works now is I get a large CSV data file. So in my code, I create a connection via ODBC to that text file and pull in some data (in one example 300K rows). Then in my workbook about 35 columns of formulas are added to the table that imports and the formulas all drag down to the bottom. I then copy/paste values all those columns since there are a ton of formulas over a lot of cells so I value them out to help the size. That worksheet (containing the original data+35 columns of formulas) is copied into a new workbook, which I automatically save as a new CSV file. I then delete the data (and query) from the original workbook. I then alter the external data connection (again via ODBC to a CSV file) for a pivot table to reflect the name/address of the new CSV file that I created (with formulas) and refresh to update the data.

Everything seems to run fine until I try and refresh the pivot table. At that point it says "ODBC exceeds system resources" and when I look at system tasks I see 1,250MB. But at that point, the connection for that pivot table has already been changed. So if I close the workbook and then re-open it I see that system tasks has Excel at only 280MB and when the workbook re-opens the pivot table/connection automatically refreshes (and since the connection has already been established) the updated data is reflected in the table.

So does this seem like a crazy process? Do you have suggestions on how I could accomplish this in a less "system-hoggy" way?

THANKS!

 

[mole999]

Any variables left with data

Dim A As
A = junk

A="" or A = 0 as appropriate

to finish off

then save so its no longer available

 

[largeselection]

WEIRD STUFF. Hopefully someon ecan explain it...

So I added a line to save the file as part of the code, and I tried changing the file type to xlsb. Didn't notice too much of a difference between .xlsb and xlsm with regard to memory use.

I monitored the mem usage as I ran the code again and it shot up to a max of 1,400MB @ 100% CPU usage naturally, and then when it was all finished it settled on 1,033MB.

Here is the weird part- so I opened another random excel file from within the same excel instance, and then closed that random file and the Excel.exe Mem usage dropped to 22MB!!

Any ideas why that would happen? If that is always going to be the case, then I could theoretically just add some code to open a random file, close it, and then re-add back my code to refresh the pivot tables.

Thoughts?

 

[largeselection]

Naturally I spoke too soon...so everything looks good in the Mem Usage section, but I just went into the table and tried to refresh it (since now it looks like mem usage is Ok). I have not closed and re-opened it yet. I got the same error though "System Resource Exceeded".

****, back to the drawing board I suppose.

 

[largeselection]

No I've cleared out the variables already. There weren't too many of them so I don't think those were driving the problem. Thanks though, every little bit helps.

 

[mole999]

so whats your base system, pentium xeon. how much memory, network bandwith, harddisk spare space, free system resources

 

[largeselection]

Not sure I know all the answers to those questions, but I'm running windows XP, with Intel Core 2 Duo @1.8 GHz, 2GB of RAM. Good network bandwidth, 46 GB free space on the HD.

As I watched it run, the real spike in Mem Usage came when it adds those 35 columns of formulas. It just doesn't seem to release the mem usage from that even after valueing out the formulas, moving that data to a new workbook, and deleting it from the original book. The only time it releases that memory is when I close it down.

Is it possible that it is keeping the formulas in memory for some reason? Is it possible to add the formulas "without undo" so that it doesn't do that?

 

[MrKowz]

Try forcing an entire clear on the clipboard. Application.CutCopyMode = False will only clear Excel data.

Taken from Chip Pearson's site: http://www.cpearson.com/excel/Clipboard.aspx

[SIZE=4]EmptyClipboard[/SIZE] 
Within Excel, you can clear Excel data from the clipboard with: Application.CutCopyMode = False. Or to completely clear the clipboard, use code like the following. 
 
 
Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long
Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
 
Sub ClearClipboard()
    OpenClipboard 0&
    EmptyClipboard
    CloseClipboard
End Sub
 
 


</PRE>The Public Declare lines of code need to be in the declarations section of the module, before and outside of any procedure. If you are going to use this code within an object module (ThisWorkbook, a Sheet module, a UserForm module, or a Class module), change Public to Private.

 

[mole999]

OK a different question

what is so important about the pivot table

I would consider

deleting the sheet it exists in

save file

add the same named sheet

build the pivot from VBA

import your data

save the file

 

[largeselection]

Interesting. Thanks both for those suggestions. I will try them both. I've added this code below and it seems to work too, but is a weird workaround.

Application.OnTime Now + TimeValue("00:00:20"), "ResumeCode"
Application.WindowState = xlMinimized
ThisWorkbook.Close True
Exit Sub
 
Sub ResumeCode()
Application.WindowState = xlMaximized
MsgBox ("DONE")
End Sub

So this essentially closes the workbook, then minimizes excel, which seems to clear the memory, then in 20 seconds it opens it up again and finishes the routine.

 

[largeselection]

Yargh!

That worked one time. But I can't repeat. Now even if I close the workbook, when I reopen it I get the "System Resources Exceeded" message when it tries to refresh.

Thought I had it there for a minute.