getting list of files
 

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

getting list of files
 

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.

getting list of files
 

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

getting list of files
 

hi, !

Perfect... Thank you Hector

thanks to you for your feed-back ;)

regards,
hector.