Most efficient way to loop through a folder and find all the XLS f

[Barb Reinhardt]

I've done this a number of ways, but am wondering what the most efficient way
is to loop through a folder. I've used DIR and File Scripting Objects.
Which is the best way?

Thanks,
Barb Reinhardt

 

[Ron de Bruin]

I like to use Dir for one folder and if I also want to loop through the
subfolders I use the other one

See the example code on my site

 

[Joel]

I think dir is the easiest way


Folder = "c:\temp\"
FName = dir(Folder & "*.xls")
Do while FName <> ""
set bk = workbooks.open(filename:=FName)
'enter your code here

bk.close savechanges:=False
FName = dir()
loop

 

[Chip Pearson]

If you want to loop through only the direct children of a directory,
Dir is probably better, or at least probably faster (perceptably
faster, though, is another quesiton). However, if you want to
recursively loop through all files and folders, subfolders, subfolders
of subfolders and so on, the FileSystemObject is much easier to work
with. At http://www.cpearson.com/Excel/RecursionAndFSO.htm I describe
exactly how the FSO can be used in a recursive manner to traverse all
children of a folder.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)