Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default folder for opening/saving files | Excel Discussion (Misc queries) | |||
Opening different files one by one in same folder | Excel Programming | |||
opening multiple xls files in a folder | Excel Programming | |||
opening order macro for files in a folder | Excel Programming | |||
"Folder Select" Dialogue - Opening multiple files from selected folder | Excel Programming |