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:
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!
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.SelectAnyway, 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!
