Loop through hundreds of files in a folder using select CASE

[satyen]

I have different excel spreadsheets that will be in a directory i.e C:/Files/ folder. I will need to do different things with a lot of the files.

e.g. All files beginning with "THER " will be of one kind. All files beginning with "APP" will be of another kind. There will about 20 other formats.

Basically I need some code to loop through a specific folder that will pick out if the file begins with for example "THER" and then action some code. The code to action will depend on which file it has detected.

CASE would be the best way and efficient way of managing the code I believe. Can anyone help?

[mdmackillop]

Use the Dir function as here . The only change being
[VBA]
Arr = Array("THER", "APP")
For Each a In Arr
MyFile = Dir("C:\AAA\" & a & "*.*")
Do Until MyFile = ""
'do something with myfile
MyFile = Dir
Loop
Next

[/VBA]

[satyen]

If it Finds a file that begins with 'THER' it needs to action some code if it finds file that begins with 'APP' it needs to action some other code and so on for 200 other formats different code needs to be actioned. Will this work. Cannot I not use select CASE with a loop like this?

[Bob Phillips]

Yes something like this untested code

[vba]

Arr = Array("THER", "APP")
For Each a In Arr
MyFile = Dir("C:\AAA\" & a & "*.*")
Do Until MyFile = ""
Select Case a
Case "THER": 'do something with myfile
Case "APP": 'do something else with myfile
End Select
MyFile = Dir
Loop
Next
[/vba]