Does anyone know how to write a simple code searching for a filename or
just a name from several workbooks & different drives by just only a
click? Thanks in advance.

John


--
jt46
------------------------------------------------------------------------
jt46's Profile: http://www.excelforum.com/member.php...o&userid=15154
View this thread: http://www.excelforum.com/showthread...hreadid=268367

If you need to search only a single directory, the code is simple. However, if
you need to search the directory tree, including any subdirectories under the
top directory, and subdirectories under those subdirectories, etc, it is not
trivial.

In the past, Bill Manville posted the code below to list all files of a
specified type in and below a top level directory. Maybe you can figure out
how to modify it for your purpose. The basic change would be that you must
pass to the routine the file name you're looking for and to save a file name
ONLY if it's a match. It would be up to you to continue or discontinue when
the first file is found.

The first sub is a demo. It puts the file names on Sheet1.

Option Base 1
Dim aFiles() As String, iFile As Integer

Sub ListAllFilesInDirectoryStructure()
Dim Counter As Integer
iFile = 0
ListFilesInDirectory "c:\test\" ' change the top level as you wish

For Counter = 1 To iFile
Worksheets("Sheet1").Cells(Counter, 1).Value = aFiles(Counter)
Next

End Sub

Sub ListFilesInDirectory(Directory As String)
Dim aDirs() As String, iDir As Integer, stFile As String

' use Dir function to find files and directories in Directory
' look for directories and build a separate array of them
' Dir returns files as well as directories when vbDirectory specified
iDir = 0
stFile = Directory & Dir(Directory & "*.*", vbDirectory)
Do While stFile < Directory
If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf GetAttr(stFile) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(iDir)
aDirs(iDir) = stFile
Else
' add to global array of files
'MAKE CHANGE HERE TO SAVE THE NAME ONLY IF IT MATCHES
iFile = iFile + 1
ReDim Preserve aFiles(iFile)
aFiles(iFile) = stFile
End If
stFile = Directory & Dir()
Loop

' now, for any directories in aDirs call self recursively
If iDir 0 Then
For iDir = 1 To UBound(aDirs)
ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
Next iDir
End If
End Sub

On Mon, 11 Oct 2004 22:05:47 -0500, jt46
wrote:


Does anyone know how to write a simple code searching for a filename or
just a name from several workbooks & different drives by just only a
click? Thanks in advance.

John