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]

sounds like too many live formulas, do you have vba that could be triggered that just places the result of the formula in a cell, rather than the updating all the time

 

[largeselection]

Thanks for looking! I don't think I have too many formulas because I actually don't have any that remain as formulas in the workbook. After I add the columns of formulas and drag them down I copy them all and paste them as values so they aren't formulas any more.

Also, I then copy that whole tab to a whole different workbook which is then subsequently saved and then closed. Then that original tab is erased.

Thanks for the suggestion!

 

[largeselection]

I also should have edited my original post since it does say that I copy the data+formulas quite a bit. They aren't actually formulas any more, I should have said data + new columns that I've added or something rather than formulas.

Sorry

 

[mole999]

my lappy struggles with 30MB lol

so the data is cleaned and placed in a CSV

then you query the CSV as a remote connection

build a piviot table

if your data hasn't changed (being thick here), why do you need to refresh the pivot.

if you force a save after importing the first time

or delete the data connection and force a save, does that work

have you tried keeping the 30,000 rows on a sheet within excel, that way you won't be swapping data over a link

 

[largeselection]

I refresh the pivot because the data will change. Basically I will get a new dataset every week and I just want to refresh the existing pivot tables but I need to swap out the data (which is in the newly created CSV with the extra columns that I add).

I haven't tried saving the workbook before the refresh of the pivot table - I've only saved, closed excel, and re-opened so I'm not sure what effect saving would have. I'll try that to see if it helps.

I've tried keeping the 300,000 rows of data in the excel workbook itself and just pivoting based on the sheet within the workbook rather than an external connection, but because of the size of the data that file then becomes large to save and work with (150MB file size).

Thanks!

 

[largeselection]

So some stats:

I opened the file - before running the macro system use = 270MB, I hit the button to run the macro. One thing the macro does is to clear out any existing connections first (since it creates new connections as part of what it does). After that step - 27MB system use. I had it minimize excel while running, then maximize excel when complete. When complete (before refreshing the pivot tables since I deleted that code since it kept failing) system use = 1,074MB. Hitting Save button only, Excel use went down to 906MB. So that was a useful tip you gave me and saved 10%!! Any other suggestions out there?

Thanks!

 

[mole999]

i was reading up on file bloat, with 300000 rows you must be in 2007, so save the file as xlsb, and see what that does, its just halved my 9Mb xlsm file, and runs perfectly well

 

[RoryA]

Do you clear the clipboard and save the file at any stage?

 

[largeselection]

I've added a couple Application.CutCopyMode = False lines to the code to be sure the clipboard is all clear. I don't have it save anywhere yet, but given that it shaved 10% off when I tried it I will probably add that. I'll see what happens with xlsb