Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting list of files
hi, !
Perfect... Thank you Hector thanks to you for your feed-back ;) regards, hector. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list all the files on my computer | Excel Discussion (Misc queries) | |||
Recently Used Files List | Excel Discussion (Misc queries) | |||
dropdown list...no help in the help files | New Users to Excel | |||
List of Files in A Directory | Excel Discussion (Misc queries) | |||
List Files in excel | Excel Discussion (Misc queries) |