making macro user friendly and flexible

[AkaTrouble]

if have created the macro included in the code below with lots of help from here

it all works ok as is currently is click /select / create and fill sheet

having shared it with a few friends (who think it is great)

a few questions from them and me

presently i have created a sheet called home and placed a button on it to run the macro

have been asked if there is anyway to pick the columns that are included / excluded from the availiable options

i could create seperate macros with only certain options included and add more buttons to home sheet

also have been asked if the sheet name could be asked for (maybe defaulting to the folder name selected)

although is easy enough to rename after creation (default folder name at least refers to what is contained)

and if a non standard option could be included which creates 2 columns one with only file name and one with extension only

this is to enable filtering and sorting by file type ( rather than the filetype called which refers to windows associated program)

lastly could the full path option be created as a hyperlink so file can be run / opened from within spreadsheet

getting this far has been brilliant in teaching me many of the advanced features of excel and the help so far has been exceptional


thanks for reading

'Force the explicit delcaration of variables
Option Explicit

Sub ListFiles()


    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)
    
    'Declare the variables
    Dim objFSO As Scripting.FileSystemObject
    Dim objTopFolder As Scripting.Folder
    Dim strTopFolderName As String
   


    
    'Assign the top folder to a variable
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "Pick a folder"
        .Show
        If .SelectedItems.Count = 0 Then MsgBox "Operation Cancelled by the user", vbExclamation + vbOKOnly, "List Files": Exit Sub
        strTopFolderName = .SelectedItems(1)
    End With
    
    ' create a new sheet
    ThisWorkbook.Sheets.Add(after:=Sheets(Sheets.Count)).Name = Mid$(strTopFolderName, InStrRev(strTopFolderName, "\") + 1)
    
    'Insert the headers for Columns A through F
    Range("A1").Value = "File Name"
    Range("B1").Value = "File Size"
    Range("C1").Value = "File Type"
    Range("D1").Value = "Date Created"
    Range("E1").Value = "Date Last Accessed"
    Range("F1").Value = "Date Last Modified"
    Range("G1").Value = "File Path"
    
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Get the top folder
    Set objTopFolder = objFSO.GetFolder(strTopFolderName)
    
    'Call the RecursiveFolder routine
    Call RecursiveFolder(objTopFolder, True)
    
    'Change the width of the columns to achieve the best fit
    Columns.AutoFit
    
End Sub

Sub RecursiveFolder(objFolder As Scripting.Folder, _
    IncludeSubFolders As Boolean)

    'Declare the variables
    Dim objFile As Scripting.File
    Dim objSubFolder As Scripting.Folder
    Dim NextRow As Long
    
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'Loop through each file in the folder
    For Each objFile In objFolder.Files
                Cells(NextRow, "A").Value = objFile.Name
        Cells(NextRow, "B").Value = objFile.Size
        Cells(NextRow, "C").Value = objFile.Type
        Cells(NextRow, "D").Value = objFile.DateCreated
        Cells(NextRow, "E").Value = objFile.DateLastAccessed
        Cells(NextRow, "F").Value = objFile.DateLastModified
        Cells(NextRow, "G").Value = objFile.Path
        NextRow = NextRow + 1
    Next objFile
    
    'Loop through files in the subfolders
    If IncludeSubFolders Then
        For Each objSubFolder In objFolder.SubFolders
            Call RecursiveFolder(objSubFolder, True)
        Next objSubFolder
    End If
    
End Sub

 

[AkaTrouble]

can i ask if you do have look and add or create any code can you please comment it so i can learn and understand what and how it is working

thank you