I want a list of files of a particular type from a folder and all its
subfolders.

The type of files I need the list of is tiff files (ie brown.tif)
but it needs to be from folder "A" and all its subfolders too.
Can this be done by a macro or by a 'filled' formula going down a
column? There is likely to be about 5000 files in all. I dont need
folder names or the files path.

Any help?
TIA

hi, !

I want a list of files of a particular type from a folder and all its subfolders.
The type of files I need the list of is tiff files (ie brown.tif)
but it needs to be from folder "A" and all its subfolders too.
Can this be done by a macro or by a 'filled' formula going down a column?
There is likely to be about 5000 files in all.
I dont need folder names or the files path.

given that you DON'T need folder names of files path

[A1] the starting folder (i.e.) = c:\my documents
[A2] a DOT & the EXTension (i.e.) = .TIF

copy/paste in a standard code module (and run the first sub):

Sub ListFiles()
Application.ScreenUpdating = False
Dim xFolder As String, xType As String
xFolder = Range("a1")
xType = Range("a2")
Columns("a").Clear
Range("a2") = xType
ListFilesIn xFolder, xType, True
Range("a1").EntireColumn.AutoFit
Range("a1") = xFolder
Debug.Print ActiveSheet.UsedRange.Address
End Sub

Sub ListFilesIn(xFolder As String, xType As String, includeSubs As Boolean)
Dim xFile, sFolder, nRow As Long
nRow = Range("a65536").End(xlUp).Row + 1
With CreateObject("scripting.filesystemobject")
With .GetFolder(xFolder)
For Each xFile In .Files
With xFile
If InStr(1, .Name, xType, 1) Then _
Range("a" & nRow) = Application.Substitute(.Name, .Path, ""): _
nRow = nRow + 1
End With
Next
If includeSubs Then
For Each sFolder In .SubFolders
ListFilesIn sFolder.Path, xType, True
Next
End If
End With
End With
End Sub

hth,
hector.

On Jul 25, 7:36*pm, "Héctor Miguel"
wrote:
hi, !

I want a list of files of a particular type from a folder and all its subfolders.
The type of files I need the list of is tiff files (ie brown.tif)
but it needs to be from folder "A" and all its subfolders too.
Can this be done by a macro or by a 'filled' formula going down a column?
There is likely to be about 5000 files in all.
I dont need folder names or the files path.

given that you DON'T need folder names of files path

[A1] the starting folder (i.e.) = c:\my documents
[A2] a DOT & the EXTension (i.e.) = .TIF

copy/paste in a standard code module (and run the first sub):

Sub ListFiles()
* Application.ScreenUpdating = False
* Dim xFolder As String, xType As String
* xFolder = Range("a1")
* xType = Range("a2")
* Columns("a").Clear
* Range("a2") = xType
* ListFilesIn xFolder, xType, True
* Range("a1").EntireColumn.AutoFit
* Range("a1") = xFolder
* Debug.Print ActiveSheet.UsedRange.Address
End Sub

Sub ListFilesIn(xFolder As String, xType As String, includeSubs As Boolean)
* Dim xFile, sFolder, nRow As Long
* nRow = Range("a65536").End(xlUp).Row + 1
* With CreateObject("scripting.filesystemobject")
* * With .GetFolder(xFolder)
* * * For Each xFile In .Files
* * * * With xFile
* * * * * If InStr(1, .Name, xType, 1) Then _
* * * * * * Range("a" & nRow) = Application.Substitute(.Name, .Path, ""): _
* * * * * * nRow = nRow + 1
* * * * End With
* * * Next
* * * If includeSubs Then
* * * * For Each sFolder In .SubFolders
* * * * * ListFilesIn sFolder.Path, xType, True
* * * * Next
* * * End If
* * End With
* End With
End Sub

hth,
hector.

Perfect, your a genius. Thank you Hector

hi, !

Perfect... Thank you Hector

thanks to you for your feed-back ;)

regards,
hector.