Macro that opens and closes many files uses too much memory

[Whizbang]

I have a macro that runs through a month's worth of reports that are exported out of the company system into Excel. My macro opens up the first report, gathers the necessary data and enters it into database format into a table. It then closes the report and opens the next one and repeats until all the month's reports are converted.

The problem is that the reports are fairly large (they are meant to be read by the human eye, and so have loads and loads of repeated strings throughout). By the end of the month's reports, the computer is being very sluggish. It seems to me that even though I close out each report, some part of it is being held in memory. Is there a way to clear out the memory usage after the report is closed? The files are opened as Read Only.

I am using a for each loop that cycles through all the cells in column A looking for certain criteria, and if matched then copy the corresponding data.

I would normally upload an example workbook, but I wouldn't even know where to begin to dummy it down.

Here is the code in question:

Please Login or Register  to view this content.

The problem is that when it gets to the CALL DDATrialExtract, the pc is so slow that it takes about an hour to run through the DDATrialExtract. When I run CIFExtract, save and close out of Excel, reopen the workbook and run DDATrialExtract it takes a few minutes.

Is there any way I could save on memory usage to make this not such a memory hog?

[Whizbang]

Well, I found one small step in the right direction by doing a few Google searches.

I just need to add

Please Login or Register  to view this content.

after the file is closed. This seems to have helped some. It still gets progessively slower, but that is probably due to the large amount of data being converted.

I tried added a line to save the data workbook, but this doesn't seem to have sped things up at all.

Any other ideas?