File listing of all files including subfolders

[mgana]

Is there a way using VBA to list all the files contained in a particular folder which automatically reads through the subfolders of that folder?
The subfolder names should not appear anywhere except in the path of the file name.
The file names have to be in the form of full path including file name with extension.

Please help.

Thanks.

 

[tusharm]

You can use either the VBA Dir function, the Windows FileSystemObject capability, or Office's FileSearch capability.

For a pre-packaged solution, check the Directory add-in available from my web site. One of the items it reports is the full filename. So, use the add-in to create a listing and delete all columns except for the one of interest to you.

 

[mgana]

I'm not a techie at all so I have little knowledge of programming terms. I would really prefer if a simple VBA code solution - perhaps a modification of some existing code?

 

[PaddyD]

search the board - you will find numerous examples, including a few by tushar. something like "files directory list" should do it

 

[nehpets12]

Try this


Sub IndexFiles()

Sheets.Add
ActiveSheet.Name = "Index"

With Application.FileSearch

' If you leave the lookin line out it will display this
' list for the current active directory.
.LookIn = "U:\scarab\production information"

.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
End With


cnt = Application.FileSearch.FoundFiles.Count

For i = 1 To cnt
rng = "A" & i
Range(rng).Value = Application.FileSearch.FoundFiles.Item(i)
Next

End Sub

 

[mgana]

The code runs - up to a point, but then halts at "Next" in the code. It would be great if a progress bar indicates the status...
Also, can the user be prompted to select a directory? Can the output include the Date Created information?

Thanks.

 

[Benjamin]

Hi ,

Here you go. This code lets you browse to the folder you want to make the list from, it then selects a sheet called file list and starts with making a list of the subfolders and their paths, it then passes the path of the top level folder to a set of code that lists all the files and their path on the same sheet.

You just need to change the sheet name. Let me know if you need any more help.

Sincerely,

Benjamin

Sub ListFolders()
'''''''''''''''''''''''''''''''''''''''Makes List of Folders and Subfolders with paths for copying'''''''''''''''

Dim fs, f, f1, s, sf
Dim iRow As Long
Dim fd As FileDialog
Dim FolderName1 As String

ExtraSlash = "\"

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
        .AllowMultiSelect = True
        If .Show Then
        
            For Each myFolder In .SelectedItems
                
                   FolderName1 = myFolder & ExtraSlash
    
    
                Set fs = CreateObject("Scripting.FileSystemObject")
                Set f = fs.GetFolder(FolderName1)
                Set sf = f.SubFolders
                
                Sheets("File List").Select
                 Range("A1").Select
                    For Each f1 In sf
                        ActiveCell.Value = f1.Name
                        ActiveCell.Offset(0, 1).Activate
                        ActiveCell.Value = f1.Path ''''''''''''''''''''''''''''''''''''''''''''''''
                        ActiveCell.Offset(0, -1).Activate
                        ActiveCell.Offset(1, 0).Activate
                        'iRow = iRow + 1
                    Next
        
            Next
        End If
End With

ListFiles FolderName1

End Sub

Sub ListFiles(FolderName1 As String)
'''''''''''''''''''''''''''''''''''''''Makes List of Folders and Subfolders with paths for copying'''''''''''''''
    Dim fs As Object
    Dim objFolder As Object
    Dim objFile As Object
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fs.GetFolder(FolderName1)
    
    Sheets("File List").Select  '''change the name of the worksheet selected'''
    Range("a1").Activate
    r = Range("A1").CurrentRegion.Rows.Count
    
    If r = 1 Then
    
      
        For Each objFile In objFolder.Files
            ActiveCell.Select
            Selection.Formula = objFile.Name
            ActiveCell.Offset(0, 1).Select
            Selection.Formula = objFile.Path
            ActiveCell.Offset(0, -1).Select
            ActiveCell.Offset(1, 0).Select
            
        Next
    
    
    Else
    
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        
        For Each objFile In objFolder.Files
            ActiveCell.Select
            Selection.Formula = objFile.Name
            ActiveCell.Offset(0, 1).Select
            Selection.Formula = objFile.Path
            ActiveCell.Offset(0, -1).Select
            ActiveCell.Offset(1, 0).Select
            
        Next
        
    End If
    
    
    Columns("A").Select
    Selection.Columns.AutoFit
    Range("A1").Select
    
''''''''''''''''''''''''''''''''''''''''''''''''
   
End Sub

 

[kevin_floyd]

how could you go into each sub folder and list the files and folders there? ( and into the folders in those folders, and so on?)

 

[tusharm]

I typically write a recursive routine for such cases. For an example of listing all files in a directory and all of its sub-directories, see http://www.mrexcel.com/board2/viewtopic.php?p=66157#66157

how could you go into each sub folder and list the files and folders there? ( and into the folders in those folders, and so on?)

 

[readit_reddit]

Hi,
Can you show me how to limit the code to only read the files that are .txt files? In addition to that, is it possible if we can make it read all the text files in the main and sub folders without manually selecting subfolders?

Thank you!

Hi ,

Here you go. This code lets you browse to the folder you want to make the list from, it then selects a sheet called file list and starts with making a list of the subfolders and their paths, it then passes the path of the top level folder to a set of code that lists all the files and their path on the same sheet.

You just need to change the sheet name. Let me know if you need any more help.

Sincerely,

Benjamin

Sub ListFolders()
'''''''''''''''''''''''''''''''''''''''Makes List of Folders and Subfolders with paths for copying'''''''''''''''

Dim fs, f, f1, s, sf
Dim iRow As Long
Dim fd As FileDialog
Dim FolderName1 As String

ExtraSlash = "\"

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
        .AllowMultiSelect = True
        If .Show Then
        
            For Each myFolder In .SelectedItems
                
                   FolderName1 = myFolder & ExtraSlash
    
    
                Set fs = CreateObject("Scripting.FileSystemObject")
                Set f = fs.GetFolder(FolderName1)
                Set sf = f.SubFolders
                
                Sheets("File List").Select
                 Range("A1").Select
                    For Each f1 In sf
                        ActiveCell.Value = f1.Name
                        ActiveCell.Offset(0, 1).Activate
                        ActiveCell.Value = f1.Path ''''''''''''''''''''''''''''''''''''''''''''''''
                        ActiveCell.Offset(0, -1).Activate
                        ActiveCell.Offset(1, 0).Activate
                        'iRow = iRow + 1
                    Next
        
            Next
        End If
End With

ListFiles FolderName1

End Sub

Sub ListFiles(FolderName1 As String)
'''''''''''''''''''''''''''''''''''''''Makes List of Folders and Subfolders with paths for copying'''''''''''''''
    Dim fs As Object
    Dim objFolder As Object
    Dim objFile As Object
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fs.GetFolder(FolderName1)
    
    Sheets("File List").Select  '''change the name of the worksheet selected'''
    Range("a1").Activate
    r = Range("A1").CurrentRegion.Rows.Count
    
    If r = 1 Then
    
      
        For Each objFile In objFolder.Files
            ActiveCell.Select
            Selection.Formula = objFile.Name
            ActiveCell.Offset(0, 1).Select
            Selection.Formula = objFile.Path
            ActiveCell.Offset(0, -1).Select
            ActiveCell.Offset(1, 0).Select
            
        Next
    
    
    Else
    
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        
        For Each objFile In objFolder.Files
            ActiveCell.Select
            Selection.Formula = objFile.Name
            ActiveCell.Offset(0, 1).Select
            Selection.Formula = objFile.Path
            ActiveCell.Offset(0, -1).Select
            ActiveCell.Offset(1, 0).Select
            
        Next
        
    End If
    
    
    Columns("A").Select
    Selection.Columns.AutoFit
    Range("A1").Select
    
''''''''''''''''''''''''''''''''''''''''''''''''
   
End Sub