List files in Folder if Condition is Met

[Filo]

Using VBA, I would like list in column A all of the .xls files included in a
generic folder whose last 5 digits before the .xls extension are greater than
5000.
Can you point me in the right direction?

Thank you.
Filo

 

[Jim Cone]

re: "Can you point me in the right direction?"

If File.Name Like "*#####.xls" Then
If Val(Right$(File.Name,9)) > 5000 Then
'do something
End If
End If
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Filo"
wrote in message
Using VBA, I would like list in column A all of the .xls files included in a
generic folder whose last 5 digits before the .xls extension are greater than
5000.
Can you point me in the right direction?

Thank you.
Filo

 

[Rick Rothstein \(MVP - VB\)]

Are you sure you meant last **5** digits are greater than 5000? Or should
the 5 have been a 4? Or perhaps the 5000 should have been 50000? Assuming
you meant what you said, this macro should do what you want....

Sub FilesToColumnA()
Dim X As Long
Dim Path As String
Dim FileName As String
Path = "c:\temp\"
' This line makes sure the path ends with a back slash
If Right(Path, 1) <> "\" Then Path = Path & "\"
FileName = Dir$(Path & "*.xls")
Do While Len(FileName) > 0
If Val(Mid(FileName, InStr(FileName, ".") - 5, 5)) > 5000 Then
Range("A1").Offset(X, 0).Value = Path & FileName
X = X + 1
End If
FileName = Dir$()
Loop
End Sub

Rick

 

[Filo]

Awesome! I tested it and it works perfectly.
Thank you Rick

PS I learned a lot from this line:
Val(Mid(FileName, InStr(FileName, ".") - 5, 5)) > 5000