Got an interesting problem.

I've written a macro which will go to a directory of your choice and look
for excel files matching a certain name criteria. It will then open each file
found and copy out data from that file to the main file, in this way creating
a summary file. The found file is then closed.

Sometimes (and only sometimes), 1 file in the directory can be "found" twice
so that the data is copied twice. This can screw things up later down the
line when I average across a set of values from different parent files.
Interesting thing is that a lot of the time the filesearch works exactly as
it should.

Here is the filesearch code in question - the full macro is way too large to
post up.
----------------------------------------------
Set fs = Application.FileSearch

' Find all corresponding files in directory in question

With fs
.NewSearch
.LookIn = direc
.Filename = fil & "*.xls"
.SearchSubFolders = searchsub
.Execute

If .FoundFiles.Count = 0 Then
MsgBox "No files in directory"
GoTo endsub
Else
MsgBox "found " & .FoundFiles.Count & " files"
End If

End With
-----------------------------------------

Also, I assume there's an easy way to open the files, extract their data and
close them again all in the background but I don't know what it is, so at the
moment my excel screen flashes while up to 50 files can be opened, searched
and closed sequentially. Not the end of the world but a bit annoying
nonetheless.

Thanks for any light that can be shed here.

The Office .FileSearch implementation seems so flakey due to a number of
random failures, it easier/more reliable to not use it.
You can get the same functionality yourself in native VBA using Dir().
The Help file gives an example to start with.

NickHK

"anathema" wrote in message
...
Got an interesting problem.

I've written a macro which will go to a directory of your choice and look
for excel files matching a certain name criteria. It will then open each
file
found and copy out data from that file to the main file, in this way
creating
a summary file. The found file is then closed.

Sometimes (and only sometimes), 1 file in the directory can be "found"
twice
so that the data is copied twice. This can screw things up later down the
line when I average across a set of values from different parent files.
Interesting thing is that a lot of the time the filesearch works exactly
as
it should.

Here is the filesearch code in question - the full macro is way too large
to
post up.
----------------------------------------------
Set fs = Application.FileSearch

' Find all corresponding files in directory in question

With fs
.NewSearch
.LookIn = direc
.Filename = fil & "*.xls"
.SearchSubFolders = searchsub
.Execute

If .FoundFiles.Count = 0 Then
MsgBox "No files in directory"
GoTo endsub
Else
MsgBox "found " & .FoundFiles.Count & " files"
End If

End With
-----------------------------------------

Also, I assume there's an easy way to open the files, extract their data
and
close them again all in the background but I don't know what it is, so at
the
moment my excel screen flashes while up to 50 files can be opened,
searched
and closed sequentially. Not the end of the world but a bit annoying
nonetheless.

Thanks for any light that can be shed here.

As Nick, I would move away from FileSearch.
Have a look at the post dated 18 July: Open files macro.
It gives you a recursive function, based on Dir, to get your list of files
in an array. From there it should be simple.

RBS

"anathema" wrote in message
...
Got an interesting problem.

I've written a macro which will go to a directory of your choice and look
for excel files matching a certain name criteria. It will then open each
file
found and copy out data from that file to the main file, in this way
creating
a summary file. The found file is then closed.

Sometimes (and only sometimes), 1 file in the directory can be "found"
twice
so that the data is copied twice. This can screw things up later down the
line when I average across a set of values from different parent files.
Interesting thing is that a lot of the time the filesearch works exactly
as
it should.

Here is the filesearch code in question - the full macro is way too large
to
post up.
----------------------------------------------
Set fs = Application.FileSearch

' Find all corresponding files in directory in question

With fs
.NewSearch
.LookIn = direc
.Filename = fil & "*.xls"
.SearchSubFolders = searchsub
.Execute

If .FoundFiles.Count = 0 Then
MsgBox "No files in directory"
GoTo endsub
Else
MsgBox "found " & .FoundFiles.Count & " files"
End If

End With
-----------------------------------------

Also, I assume there's an easy way to open the files, extract their data
and
close them again all in the background but I don't know what it is, so at
the
moment my excel screen flashes while up to 50 files can be opened,
searched
and closed sequentially. Not the end of the world but a bit annoying
nonetheless.

Thanks for any light that can be shed here.