It is difficult to critique imaginary code...
Do not use "New" in a variable declaration - see Chip Pearson's website if
you want the why of that.
ActiveSheet is an unqualified reference. Do not use it without specifying
the application it belong to. If fact I would not use it at all.
Suggested format for automation code...

Dim xlApp as Excel.Application
Dim WB as Excel.Workbook
Dim WS as Excel.Worksheet
Dim bigRange as Excel.Range
Set xlApp = New Excel.Application

Start of loop
Set WB = xlApp.Open(FileName)
Set WS = WB.Worksheets(1)

Do stuff using WS.Range("xxxx") or Set a reference to the range...
Set bigRange = WS.Range("xxxx")
(do not use "Selection" or "ActiveCell" or anything similar)
(do not use the "With" construct - use an object reference)

Set the worksheet and range references to Nothing
Close the Workbook and specify whether to save it or not.
Set the Workbook reference to Nothing
End of Loop

After looping thru all of the files, quit the application and set it to nothing.
'--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
Hi Jim,
I am trying this method because I need to import data from several hundred
small excel files to process a summary of the data. The files are random in
nature with data scattered throughout. When I load the data using the
current instance, it doesn't seem to let go of the memory and by the 300th
file, it takes about 60x as long just to read the data into an array... and
the files are pretty small. There may be some unqualified references in the
original code but were talking about 50 different routines and functions... a
needle in a haystack. This method has shown some promise because the routine
to read in the file data is isolated.

Do I have Orphan References in my Example?
As you can see from my example code (which is a direct paste from VBA), I
don't have an references that I would consider to be Orphan.... it's about as
simple as possible. I could even shrink it down to just Open & Close the
file and the Instance still remains.

On a side note, Pardon my lack of knowledge of orphan references...
If I have something that says ActiveSheet, can I just replace it with
something like this:

set MySheet = ActiveSheet
x = MySheet.Range("A1").value
set MySheet = Nothing

as oposed to just: x = ActiveSheet.Range("A1").Value

Thanks for the help,
MikeZz

"Jim Cone" wrote:


Why then are you opening another instance of Excel?
What happens when you run your code in the existing instance of Excel?

As far as the new instance of Excel remaining open -
a common cause is the creation of "orphan" references that prevent Excel from closing.
Those can result from the use of unqualified references such as ActiveSheet or Range ("A1").
--
Jim Cone
Portland, Oregon USA



"MikeZz"
wrote in message
I'm using Excel 2003 with SP3 installed.



"Jim Cone" wrote:
Which office application are you using to run your code?
--
Jim Cone
Portland, Oregon USA