Not familiar with TaskState; are you referring to VBA? If you do not know
the range in advance you would need to look at the specific workbook's .Names
collection. The only way I know how to do this in VBA is
Dim NewWB as Workbook, CheckName as Name
Set NewWB = Workbooks.Open (FoundFiles(i))
For Each CheckName in NewWB.Names
' Do whatever you need to do with the range
Next CheckName

--
- K Dales


"George" wrote:

K Dales,

Thanks for the reply. "ThisWorkbook.Path" was great, I can't believe I
didn't check something like that.

Now that I can loop around the current directory can I get the named range
from the current file (.FoundFiles(i)) from within the macro?

I'm presuming I can do something like the following:

taskStates(i) = .FoundFiles(i)!TaskState

???

Thanks


"K Dales" wrote:

As for the 2nd question: It is not really easier (you use either
Range(Address) or Range(Name)) but in some ways better to use the named
range. If you have to change the structure of the workbook at any time the
cell address may change but the name will remain the same, so you would have
an easier time maintaining the code if you use named ranges.
--
- K Dales


"George" wrote:

Hi there,

I want to search for files in (and below) the directory that the current
document is in. Then I need to pull out a couple of bits of information from
each file and bring those values in onto one sheet in the current Excel
document.

I need to know what to set ".LookIn" to so that it searches from the current
directory. I know to set .SearchSubFolders to true and I know how to loop
around the files that it finds. My problem is setting the .LookIn property
correctly.

Is it going to be easier to pull the information from each file is the cells
are named? That shouldn't be a problem as I have control over the files.

Thanks
George