Dim strDirPath, strMaskSearch, strFileName as StringstrDirPath = "C:/test/" 'Папка поиска
strMaskSearch = "*.xls*" 'Маска поиска
 
'Получаем первый файл соответствующий шаблону
strFileName = Dir(strDirPath & strMaskSearch) 
 
Do While strFileName <> "" 'До тех пор пока файлы "не закончатся"
    MsgBox strFileName 
    strFileName = Dir 'Следующий файл
Loop

Function Count_Lines(FilePath As String) As Long
Dim fsoFile As Scripting.FileSystemObject
Dim fsStream As Scripting.TextStream
    'Create a new File system Object
    Set fsoFile = New Scripting.FileSystemObject
    'Open the file for reading
    Set fsStream = fsoFile.OpenTextFile(Filename:=FilePath, IOMode:=ForReading)
    'Move to the bottom of the file
    Do Until fsStream.AtEndOfStream
        fsStream.SkipLine
    Loop
    'Return the current line number,
    'Which will equal the count as
    'we are at the end of the file
    Count_Lines = fsStream.Line
    'Clear Object Variables
    Set fsStream = Nothing
    Set fsoFile = Nothing
End Function