vba: getting list of all files with ESY extension in a directory

Question

in vba how do i get a list of all the files with a specific extension in a specific directory?

i am unable to do application.filesearch, because i am using excel 2007

Answer 1

Stupid MS....

Check http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/a450830d-4fc3-4f4e-aee2-03f7994369d6 for a discussion of the issue.

Answer 2

Dir("C:\yourPath\*.ESY", vbNormal) Returns the first file with esy extension. Each subsequent call to Dir() returns the next.

Answer 3

In response to your comment "great, so how many times do i know to run it?" ... this example runs until it lists all the files which match strPattern. Change the strFolder constant.

Public Sub ListESY()
Const strFolder As String = "C:\SomeFolder\"
Const strPattern As String = "*.ESY"
Dim strFile As String
strFile = Dir(strFolder & strPattern, vbNormal)
Do While Len(strFile) > 0
    Debug.Print strFile
    strFile = Dir
Loop
End Sub

Answer 4

Alternative option: use the "Microsoft Scripting Runtime" library (check it in Tools...References) for the FileSystemObject family of objects. Something like the following, perhaps:

Public Function ESYFileCount(dir_path as String) as Long

Dim fil As File

    With New FileSystemObject
        With .GetFolder(dir_path)
            For Each fil In .Files
                If LCase(Right(fil.Name, 4)) = ".esy" Then
                    ESYFileCount = ESYFileCount + 1
                End If
            Next
        End With        
    End With

End Function

Answer 5

Allen Browne has some good code on this that does more than you asked for:

List files recursively