File System Object Query

[cameronb]

I am using the below code to list files in folders and subfolders.

I only wish to list xls files, is there anyway that myfile can be limited to only xls files?

Can data be retrieved from the workbooks using the below without opening the files?

Cells(iRow, iCol).Value = myFile.Sheet1.Range("A1").Value
iRow = iRow + 1


Many thanks for any help

Dim iRow
Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
        Cells(iRow, iCol).Value = myFile.Sheet1.Range("A1").Value
        iRow = iRow + 1
        Cells(iRow, iCol).Value = myFile.Sheet2.Range("A1").Value
        iRow = iRow + 1
        
        
        
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub
[CODE/]

 

[RoryA]

Why not use Dir("c:\some path\*.xls") to limit the return file names? If you want to use FSO, then test if Right(myfile.name, 4) = ".xls"