M
myemail.an
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
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