Searching for All Files Containing a Specific Extention in a Folder and Subfolders

Question

I understand that the answer to this question may be similar to another, but the question is posed in a different way. This question is based on the fact that the user, me, did not know FileSearch was removed. The other is conceptually based, and contains prior knowledge of excel's 2010 changes...

I have found some code here

Sub Search()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objSearch = objExcel.FileSearch
objSearch.Lookin = "D:\Music"
objSearch.SearchSubfolders = TRUE
objSearch.FileName = "*.wma"
objSearch.Execute

For Each strFile in objSearch.FoundFiles
    Wscript.Echo strFile
Next

objExcel.Quit
End Sub

I tried to run that code on my machine, with it adapted to one of my folders and an extention within the folder, but it returned an error 445 (object doesn't support this action). I'm using excel 2010.

Does anyone know what's going on? I'm trying to help out a co-worker, but I don't know much about File I/O beyond the simple stuff in VBA.

Using wildcard with `Dir` covered at http://stackoverflow.com/q/10380312/641067 — brettdj, Jun 21 '14 at 02:53

citizenkong · Accepted Answer · 2014-06-21T07:14:43.570

FileSearch was removed from VBA in Office 2007. Thankfully it's not difficult to create your own routine for searching files using the FileSystemObject (add the Windows Scripting Runtime as a reference to get Intellisense code hints).

This is the one that I use - your list of files will be returned as a Collection by the FileList function. It should be simple to add a filter to this to only populate the collection with files of a particular extension.

[Note that you'll need to add the Windows Scripting Runtime reference as mentioned above since the objects are early bound in my example]

Function FileList(Path As String) As Collection

    Dim FSO as New Scripting.FileSystemObject
    Dim StartingFolder As Scripting.Folder
    Set StartingFolder = FSO.GetFolder(Path)

    Set FileList = New Collection
    RecursiveGetFiles StartingFolder, FileList

End Function


Private Sub RecursiveGetFiles(StartingFolder As Scripting.Folder, ByRef FullFileList As Collection)

    Dim File As Scripting.File
    For Each File In StartingFolder.Files
        FullFileList.Add File, File.Path
    Next File

    Dim SubFolder As Scripting.Folder
    For Each SubFolder In StartingFolder.SubFolders
        RecursiveGetFiles SubFolder, FullFileList
    Next SubFolder

End Function

This code can then be called by some parent routine, i.e.

Sub Search(Path As String)

     Dim ListOfFiles As Collection
     Set ListOfFiles = FileList(Path)

     Dim File As Scripting.File
     For Each File In ListOfFiles
         Debug.Print File.Name
     Next File

End Sub

Paste it into a standard module - Functions are like Subs except they can return a value rather than just carrying out a process; the RecursiveGetFiles could actually be a Sub rather than a function in this instance, since it modifies the FullFileList Collection as it is passed into the function by reference. — citizenkong, Jun 20 '14 at 13:39
You'll have to write another routine (a Sub) to call the function. Your Sub should contain a Collection object (i.e. Dim ListOfFiles As New Collection), then set that collection to be the result of FileList (Set ListOfFiles = FileList([some directory here])). that collection will contain all of the found files (as Scripting.File objects) so you can do what you need to do with them. — citizenkong, Jun 20 '14 at 13:44
I apologize, but can you write that into your answer. I tried, but am getting an error in the `private function` — Tanner, Jun 20 '14 at 13:50

score 1 · Answer 2 · answered Jun 20 '14 at 13:51

1

Sub Search()
Dim StrFile As String, Path As String, FileName As String

Path = "D:\Music"
FileName = "*.wma"
StrFile = Dir(Path & FileName)
Do While Len(StrFile) > 0
    Msgbox StrFile 
    StrFile = Dir
Loop
End Sub

answered Jun 20 '14 at 13:51

user3748026

31
2
7

Searching for All Files Containing a Specific Extention in a Folder and Subfolders

2 Answers2