File Name Select Case
 

Hi,

I am searching for files on certain folder, and then, depending on the
filename, I want the code to do something specific for these files.
I am using a File Name Select Case which is not working. All files should
fall into one of the 3 categories of the select case, but they are not.

This is my code:

Sub FilesUpdate ()

Dim FName As String
Dim Path As String

Path = "C:\Pricing\Product1\1st May 2007"

With Application.FileSearch
.NewSearch
.LookIn = Path
.FileName = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
FName = .FoundFiles(i)

Select Case FName

Case "*GC*"
UpdateGCFiles

Case "*PL*"
UpdatePLFiles

Case "*VC*"
UpdateVCFiles

End Select
Next
End With
End Sub

Any suggestions will be highly appreciated.

File Name Select Case
 

Hi Corey,

Try something like:

'=============
Public Sub FilesUpdate()
Dim Fname As String
Dim Path As String

Path = "C:\Pricing\Product1\1st May 2007"

With Application.FileSearch
.NewSearch
.LookIn = Path
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
Fname = .FoundFiles(i)
Select Case True
Case UCase(Fname) Like "*GC*"
UpdateGCFiles
Case UCase(Fname) Like "*PL*"
UpdatePLFiles
Case UCase(Fname) Like "*VC*"
UpdateVCFiles
End Select
Next i
End With
End Sub
'<<=============


BTW, the FileSearch property has often been
reported as problematic and, indeed, has been
dropped fron XL2007. Perhaps, therefore,
consider using Dir function,

---
Regards,
Norman


"Cavy" wrote in message
...
Hi,

I am searching for files on certain folder, and then, depending on the
filename, I want the code to do something specific for these files.
I am using a File Name Select Case which is not working. All files should
fall into one of the 3 categories of the select case, but they are not.

This is my code:

Sub FilesUpdate ()

Dim FName As String
Dim Path As String

Path = "C:\Pricing\Product1\1st May 2007"

With Application.FileSearch
.NewSearch
.LookIn = Path
.FileName = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count
FName = .FoundFiles(i)

Select Case FName

Case "*GC*"
UpdateGCFiles

Case "*PL*"
UpdatePLFiles

Case "*VC*"
UpdateVCFiles

End Select
Next
End With
End Sub

Any suggestions will be highly appreciated.

File Name Select Case
 

Hi Corey,

Apologies; s/be:

Hi Cavy,


---
Regards,
Norman

File Name Select Case
 

Excellent!!!

Thank you very much Norman.


"Norman Jones" wrote:

Hi Corey,

Apologies; s/be:

Hi Cavy,


---
Regards,
Norman