Import File Memory Leak

I understand that multiple threads of this question has already been posted by me. They contain wrong information and I have asked for them to be deleted.

Problem: I have a Workbook containing two worksheets "X" and "FILES". The worksheet "FILES" contains the name of each file in a directory on my desktop "C:\Users\Owner\Desktop\DATA-X" in cells (A1:A1000) (There are 1000 files in the folder "DATA-X". The worksheet "X" is a blank worksheet.

The following code loops 1000 times, each time importing the "k"th file from the folder "DATA-X" and storing it in the worksheet "X". After each loop, the worksheet "X" gets replaced with the data in the next file from the folder "DATA-X". As the program is running, it can be seen in the task manager that memory is accumulating in the Excel program. In fact, it cannot finish because it will use up all memory by then (I get a message "OUT OF MEMORY"). Why is this memory leak occuring and how can I fix this? Any help would be greatful.

Please Login or Register  to view this content.

I have tried everything and came up short. Does anyone have suggestions?

I think this has more to do with EXCEL itself than the actual VBA macro I am running. For example, when I open my workbook, EXCEL utilizes about 27,000K. When I run the macro, I can see the memory increasing at about 1000K/sec. When it gets to about 100,000K things start to slow down considerably and I hit the escape key to terminate the VBA macro. The thing is though, once I terminate the macro, EXCEL does not revert back to 27,000K. It is still operating at 100,000K despite the macro terminating. Any ideas?