How to get 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

In response to your comment "so how many times do i know to run it?", this example runs until it lists all the files whose names 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 '<- view this in Immediate window; Ctrl+g will take you there
    strFile = Dir
Loop
End Sub

Answer 2

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