Re: Searching a filename from multiple workbooks or drives
From: Myrna Larson (anonymous_at_discussions.microsoft.com)
Date: 10/12/04
- Next message: Maxwell: "VBA code striped off?"
- Previous message: Myrna Larson: "Re: Application.worksheetfunction"
- In reply to: jt46: "Searching a filename from multiple workbooks or drives"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 11 Oct 2004 23:57:06 -0500
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 <jt46.1dztun@excelforum-nospam.com>
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
- Next message: Maxwell: "VBA code striped off?"
- Previous message: Myrna Larson: "Re: Application.worksheetfunction"
- In reply to: jt46: "Searching a filename from multiple workbooks or drives"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
