getting list of files

  • Thread starter Thread starter Esradekan
  • Start date Start date
E

Esradekan

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.
Click to expand...

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.
 
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.
Click to expand...

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.
Click to expand...

Perfect, your a genius. Thank you Hector
 
Back
Top