Hi Joel,

Kathy did say in her second post that she had:

"... 3 sets of about 100 csv files ..."

not .xls files.

Pete

On Mar 28, 10:59*am, Joel wrote:
The following code will get every XLS file in every subdirectory starting at
c:\temp. *Change as necessary. *It will return cell B2 from the active
worksheet in each of the files. *if a diffferent sheet is needed then the
code need a small modification.

Sub findfile()
'set MyFilename and strfold as required

RowCount = 1
'directory to start searching
strFolder = "c:\temp"
Application.EnableEvents = False
* *file_loc = ""
* *Set fso = CreateObject _
* * * ("Scripting.FileSystemObject")
* *Set folder = _
* * * fso.GetFolder(strFolder)

* *Call GetWorksheetsSubFolder(strFolder + "\", _
* * * MyFileName, file_loc, RowCount)
Application.EnableEvents = True
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName, ByRef file_loc, ByRef
RowCount)
* *Set fso = CreateObject _
* * * ("Scripting.FileSystemObject")

* *Set folder = _
* * * fso.GetFolder(strFolder)

* * * If folder.subfolders.Count 0 Then
* * * * *For Each sf In folder.subfolders
* * * * * * On Error GoTo 100
* * * * * * Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", _
* * * * * * * *MyFileName, file_loc, RowCount)
100 * * *Next sf
* * * End If
* *'folder size in bytes
* *On Error GoTo 200
* * * For Each fl In folder.Files
* * * * *If UCase(Right(fl.Name, 4)) = ".XLS" Then
* * * * * * Workbooks.Open Filename:=fl
* * * * * * Set newbk = ActiveWorkbook
* * * * * * With ThisWorkbook.Sheets("Sheet1")
* * * * * * * *.Range("A" & RowCount) = newbk.ActiveSheet.Range("B2")
* * * * * * * *.Range("B" & RowCount) = fl.Name
* * * * * * * *.Range("C" & RowCount) = folder.Path
* * * * * * * *RowCount = RowCount + 1
* * * * * * End With
* * * * * * newbk.Close savechanges:=False
* * * * *End If
* * * Next fl
200 * On Error GoTo 0

End Sub



"KathyJean" wrote:
I have three sets of data about 100 apiece and the file names are different.

The format for each file is exactly the same because it's data from the same
test but I only need the one cell of information

"Joel" wrote:

Is it the same file name in each directory? *A macro can be written to search
down every subdirectory for certain files. *Are we looking for every excel
file in all sub-directories, or oinly certain filenames? *I have code that
will do what you need, but it will require some minor modification. *Just
want to know all the details before I post this code.

The code below searches for a certain filename in all subdirectories. *it
would need to be modified to open these files and extract certain data..

Public file_loc As String
Sub findfile()
'set MyFilename and strfold as required

'file to search for
Const MyFileName = "xyz.txt"
'directory to start searching
strFolder = "c:\temp"
* *file_loc = ""
* *Set fso = CreateObject _
* * * ("Scripting.FileSystemObject")
* *Set folder = _
* * * fso.GetFolder(strFolder)

* *Call GetWorksheetsSubFolder(strFolder + "\", MyFileName)

* *MsgBox ("File found in folder: " & file_loc)
End Sub

Sub GetWorksheetsSubFolder(strFolder, MyFileName)
* *Set fso = CreateObject _
* * * ("Scripting.FileSystemObject")

* *Set folder = _
* * * fso.GetFolder(strFolder)

* * * If folder.subfolders.Count 0 Then
* * * * *For Each sf In folder.subfolders
* * * * * * On Error GoTo 100
* * * * * * Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", MyFileName)
* * * * * * If file_loc < "" Then Exit For
100 * * *Next sf
* * * End If
* *'folder size in bytes
* *On Error GoTo 200
* * * If file_loc = "" Then
* * * * *For Each fl In folder.Files
* * * * * * If fl.Name = MyFileName Then
* * * * * * * *file_loc = folder.Name
* * * * * * End If
* * * * *Next fl
* * * *End If
200 * On Error GoTo 0

End Sub

"KathyJean" wrote:

Maybe I'm saying it wrong...:)

I know now I've seen posts similar to this on the site but my programming
prowess isn't that pronounced so I could use some help.

I have 3 sets of about 100 csv files and I need one datum from the same cell
in each. (For reference the cell has the time for a test performed)

Each of the file sets are in separate folders and the order doesn't matter.
The numbers of files that each folder has is different.

I think that about covers it. Let me know if I left anything out. Thanks for
the help.
*Sent at 11:35 AM on Thursday

"KathyJean" wrote:

I want to take the data from one particular cell (i.e., B2) from a multiple
of workbooks and have that data put in a new workbook. I don't want to have
to open each individual file, since there are over 300. *Is there a formula
for this?

Thanks,
Kathy- Hide quoted text -

- Show quoted text -