If you are searching a relatively small number of directories, you can
roll your own File Search using the Scripting FileSystemObject object.
The code below does this and runs rather quickly if you are not
searching many thousands of locations.
The values in the SearchControl Type dicatate how to run the search.
The params are:
FileNameTemplate = a file name template with optional wildcards but no
folder information (e.g., "*.xls", not "C:\*.xls"). The file name is
tested with the Like operator, so use a compatible expression.
Count = initialize to 0. Upon completion contains the number of found
files.
CurrCount = initialize to 0. Used for smart array reallocation. No
meaningful info upon completion.
ArrReallocLimit = the number of elements between array reallocation.
Use 100 unless you have reason to change it.
Recurse = True to recurse through subfolders. False to search only the
specified folder.
FileNameOnly = True to return only file names with no path info (e.g.,
"Book1.xls"), False to return fully qualified file names (e.g.,
"C:\Test\Folder\Book1x.xls")
FSO = initialize to new instance Scripting.FileSystemObject.
FoundFiles = upon completion, an array of the found file names.
Put the following code in a reqular module:
'==========================================
' START CODE
'==========================================
Type SearchControl
FileNameTemplate As String
Count As Long
CurrCount As Long
ArrReallocLimit As Long
Recurse As Boolean
FileNameOnly As Boolean
FSO As Scripting.FileSystemObject
FoundFiles() As String
End Type
Sub XFileSearch(ByVal SearchFolder As Scripting.Folder, _
ByRef Data As SearchControl)
Dim FF As Scripting.Folder
Dim F As Scripting.File
With Data
For Each F In SearchFolder.Files
If F.Name Like .FileNameTemplate Then
.Count = .Count + 1
.CurrCount = .CurrCount + 1
If .FileNameOnly = True Then
.FoundFiles(.Count) = F.Name
Else
.FoundFiles(.Count) = F.Path
End If
If .CurrCount = .ArrReallocLimit Then
.CurrCount = 0
ReDim Preserve .FoundFiles _
(1 To .Count + .ArrReallocLimit)
End If
End If
Next F
If .Recurse = True Then
For Each FF In SearchFolder.SubFolders
XFileSearch FF, Data
Next FF
End If
End With
End Sub
'==========================================
' END CODE
'==========================================
You can the call the XFileSearch proc with code like the following:
'==========================================
' START CODE
'==========================================
Sub FileSearch()
Dim Data As SearchControl
Dim FirstFolder As Scripting.Folder
Dim N As Long
With Data
.ArrReallocLimit = 100
.Count = 0
.CurrCount = 0
.FileNameOnly = False
.FileNameTemplate = "*.xls"
Set .FSO = New Scripting.FileSystemObject
.Recurse = True
ReDim .FoundFiles(1 To .ArrReallocLimit)
End With
Set FirstFolder =
Data.FSO.GetFolder("C:\BinderListAndProductKeys")
XFileSearch FirstFolder, Data
With Data
If Data.Count > 0 Then
ReDim Preserve .FoundFiles(1 To .Count)
Debug.Print "Files Found: " & Format(.Count, "#,##0")
For N = LBound(.FoundFiles) To UBound(.FoundFiles)
Debug.Print .FoundFiles(N)
Next N
Else
Debug.Print "no files found"
End If
End With
End Sub
'==========================================
' END CODE
'==========================================
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Thu, 5 Nov 2009 18:48:32 -0500, "John" <(E-Mail Removed)> wrote:
>I have recently updated to Excel2007. Great.!!! Several days ago I posted in
>this forum a stupid post. I assumed my problems were due to 32 bit vs. 64
>bit application. My bad.
> Unfortunately I have several macros which have been operating since 2001
>which depend on "application.filesearch". It has been eliminated in Excel
>2007. So far, all of my search efforts have not found a work-around that can
>be rapidly applied to my existing code.
>
>Has anyone found a solution??
>
>John
Similar Threads
