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

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

this question has reply here:

get list of subdirs in vba 4 answers

i understand reply question may similar another, question posed in different way. question based on fact user, me, did not know filesearch removed. other conceptually based, , contains prior knowledge of excel's 2010 changes...

i have found 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 each strfile in objsearch.foundfiles wscript.echo strfile next objexcel.quit end sub

i tried run code on machine, adapted 1 of folders , extention within folder, returned error 445 (object doesn't back upwards action). i'm using excel 2010.

does know what's going on? i'm trying help out co-worker, don't know much file i/o beyond simple stuff in vba.

filesearch removed vba in office 2007. thankfully it's not hard create own routine searching files using filesystemobject (add windows scripting runtime reference intellisense code hints).

this 1 utilize - list of files returned collection filelist function. should simple add together filter populate collection files of particular extension.

[note you'll need add together windows scripting runtime reference mentioned above since objects bound in example]

function filelist(path string) collection dim fso new scripting.filesystemobject dim startingfolder scripting.folder set startingfolder = fso.getfolder(path) set filelist = new collection recursivegetfiles startingfolder, filelist end function private sub recursivegetfiles(startingfolder scripting.folder, byref fullfilelist collection) dim file scripting.file each file in startingfolder.files fullfilelist.add file, file.path next file dim subfolder scripting.folder each subfolder in startingfolder.subfolders recursivegetfiles subfolder, fullfilelist next subfolder end function

this code can called parent routine, i.e.

sub search(path string) dim listoffiles collection set listoffiles = filelist(path) dim file scripting.file each file in listoffiles debug.print file.name next file end sub

excel excel-vba file-io