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

You can get the current workbook's directory simply by ThisWorkbook.Path
--
- 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

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

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

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

Perhaps I misunderstood: Is TaskState the name of your range? (I presumed it
was a property you were referring to)

If so, you could open the workbook as shown above and then
TaskStates(i) = NewWB.Names("TaskState").Value
NewWB.Close

To avoid flicker either set screen updating off or do this in a separate,
invisible Excel session.

Or you could use either DDE or an ADO query to get the value - but those you
would need to research
--
- 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