Putting a cork on memory leaks...

war4peace

New Member
Joined
Mar 13, 2009
Messages
21
I have developed a pretty large VBA project which started to eat up more and more memory as it runs, so I am interested in finding various methods to stop the flood.
Some background info:
- the project imports data from a variety of sources: HTML files, Thunderbird local folders, e-mail messages (using Outlook), text files and so on.
- It also creates a series of Pivot Tables and Charts which are saved as HTML files (they are accessed by customers via a browser).
- It runs continuously, meaning the script restarts every 2 minutes after finishing work, 24/7, no breaks :)
- Almost no data is static, except for two sheets (out of 17) which contain some static data which are used by the script.
The problem:
While running, EXCEL.EXE process starts slow with 20 MB memory occupancy; but after like 16-20 hours of running, it takes up well over 1 GB of memory and eventually errors out due to lack of available memory. Now I have had some problems identifying exactly why is it taking up memory, but was unable to find out exactly what is the root cause, hence I am in need of some advice.
The problem worsened after I added some code which generates Pivot Tables and then generates charts using data from those pivot tables.
To create the PivotTable and chart I am using the following code:
Code:
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Range(Cells(1, 1), Cells(MaxRows, 11)), Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="TSReports!R2C1", TableName:="Status", _
        DefaultVersion:=xlPivotTableVersion10
    Sheets("TSReports").Select
    Cells(2, 1).Select
    ActiveSheet.Shapes.AddChart.Select
There are more similar Pivot Tables and charts being generated by using similar code.
Anyway, what I am trying to find out is if there's a way to clear all "cached" stuff that Excel keeps after script finishes. Since Excel apparently keeps adding charts but keeps the unused ones cached somehow, maybe that's what the problem is. But I would like to go a step further and remove everything that is not used, even worksheets (which I can create when needed via VBA). So is there a global method to clear all pivot tables, charts, data conenctions and so on?
Alternatively, I could add code to delete all those, but I need some help into which commands should I use or at least where to look for them.
I am a self learner and all i know was learned by googling around and checking documentations, so please excuse my mix of knowledge and ignorance, of which the latter seems to prevail :)
Thank you!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

war4peace

New Member
Joined
Mar 13, 2009
Messages
21
No ideas from anyone?I will try implementing sheet deletion/re-creation within the code today; also I think saving the file after each run would help, as well as disabling undo.
 

Forum statistics

Threads
1,147,751
Messages
5,742,985
Members
423,771
Latest member
dimyak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top