Opening and processing all files in a folder
 

Hi all,

I used to rely on Application.filesearch to open and process all files
in a given folder. However, this functionality is no longer supported
by Excel 2007. I find it extremely irritating that Microsoft does not
care about backwards compatibility, and that it does not even bother
to explain these issue clearly, nor to mention a possible alternative
(Excel VBA help is very poor and incomplete).

Anyway, I wanted to share the alternative I found, and of course I'd
be interested in hearing any feedback (I'm sure there are other
solutions):

thefile = Dir(MyFolder & "\")

FileNumber = 0
'we must specify a variable thefile=dir and then set the while
condition on it, non directly on dir
'otherwise dir would be called more times than needed
Do While thefile < ""

'full path of the file being analyzed
thefilepath = MyFolder & "\" & thefile

'counter to update the status bar showing the progress
'since this is a do... loop (and not a for ... next) we don't know
how many files are in the path
'before running the whole cycle
FileNumber = FileNumber + 1
Application.StatusBar = "Processing file " & FileNumber

Workbooks.Open (thefilepath)
CurrentlyOpenFile = ActiveWorkbook.Name

' do whatever you have to do on the file

'closes the file
Workbooks(CurrentlyOpenFile).Close saveChanges:=False ' or
SaveChanges:= True, depending on your needs

'searches the folder again to check if there is any other file
thefile = Dir()
Loop