Memory issues

[mehman]

I am using Access 2007 and VBA to process around 200 Excel reports.

The process is a follows

1. open excel file
2. populate excel file via an Access 2007 query via vba
3. close file
4. repeat steps 1 to 3 for rest of the files

The problem I am facing is, the memory used by excel increases as more files are being processed, touching around 800 MB by the time the program nears finishing.

I do use a lot of global variables in my program. These global variables are replaced with the values for the corresponding excel file being processed

Any suggestions to reduce memory used by Excel.

Thanks in advance,
Mandeep

 

[NARAYANK991]

Hi Mandeep ,

A general question can be answered generally , especially a question such as the one you have asked.

Memory is allocated based on how you have declared your variables , and remains in use till it is disposed off within code , or when its lifetime is over by code termination.

See the following links for more helpful information :

http://www.decisionmodels.com/memlimitsb.htm

http://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx

http://chandoo.org/wp/2012/03/22/vba-macros-optimization-techniques/

Memory can vary significantly depending on your algorithm ; if you can upload your workbook with the code in it , more specific answers may be possible.