Excel File Explorer

Excel File Explorer gives the opportunity to dig into a given directory and extract the whole hierarchy of folders and files into a worksheet. The result shows indented folder names, and the name and size of the files they contain.

Folder Picker

Running the Excel File Explorer macro will trigger a dialog box to select the folder we want to explore. That’s achieved with the FileDialog property of the Application object as indicated below.

 

  Dim myDialog As FileDialog

  Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)

 

 

The variable “HostFolder” will hold the name of the selected folder including the whole drive path. The following code checks if a folder was selected, in that case it assigns its name to the variable “HostFolder”, and otherwise it exits the procedure.

 

  If myDialog.Show = -1 Then

    HostFolder = myDialog.SelectedItems(1) & Application.PathSeparator

  Else

    Exit Sub

  End If

 

Folder Files

We use the CreateObject VBA function to define “FileSystem”, previously declared as an object variable. Then we get the folder object under the path name in “HostFolder” with the GetFolder method as follows:

 

  Set FileSystem = CreateObject("Scripting.FileSystemObject")

  Set Folder = FileSystem.GetFolder(HostFolder)

 

Excel File Explorer loops through all files within the selected folder with a For Each loop, and lists the name and size of the files in columns A and B of the active sheet respectively.

 

  For Each File In Folder.Files

    ListFiles

  Next File

 

 

 

  Private Sub ListFiles()

    On Error Resume Next

    r = r + 1

    Range("A" & r).IndentLevel = IndLev

    Range("A" & r).Value = File.Name

    Range("B" & r).Value = FileLen(File)

    TotalFiles = TotalFiles + 1

    TotalSize = TotalSize + FileLen(File)

  End Sub

 

 

Folder Subfolders

Similarly to the previous step, we can loop through the subfolders within the main folder using a For Each loop, and then list all files in that subfolder. Subfolders are highlighted in bold, and the files within the subfolder share the same indent level.

 

  For Each SubFolder In Folder.Subfolders

    …

  Next SubFolder

 

At that point we need to update the folder object with the new folder path, which is stored in the variable “NewFolder”, and then repeat the whole process. For that, we use the GoTo function linked to the ‘NextFolder’ handle.

Meanwhile, IndLev holds the value of the range indent level, and increases or decreases as we dig into or out of folders in the hierarchy. The array FolderNo() is used to sort the folders for a given hierarchy level (which is represented by the indent level variable IndLev).

Total Files and Size

The procedure “ListFiles” assigns the name and size of each file to rows in the active sheet. When doing that, it also keeps the count of files and file size with the variables ‘TotalFiles’ and ‘TotalSize’ respectively. When ready, the Excel File Explorer will display a message box showing the total number of files and size of the selected directory folder.

Click Here to download Excel File Explorer.