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

Suppose you wanted to do this for just one external file. Then you would
have a formula like this in A1:

='pathname[filename.xls]sheetname'!B2

where pathname is the full path to the file, and so the entry may be
something like:

='C:\Documents and Settings\Username\My
Documents\_Clients_A_C\ABC\analysis\[PA4_0711.xls]Savings'!$B$2

Imagine doing that 300 times?

Pete

C:\
"KathyJean" wrote in message
...
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

You need a list of all the files to get the data from which probably means
you need a macro. It is probably better to have a macro open each file(s)
from a list of directories and put the data in the new workbook. You
probably also want to obtain additional information like the workbook name
where the data can from.

"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

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

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

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

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

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 -

Right. She need to change XLS to CSV (make usre it is capital letters).

"Pete_UK" wrote:

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 -