Excel tutorials

How to display data for all files in the directory and subdirectories

I believe you had the need to find out which files are in a specific directory or subdirectory. Personally, through creating my web tutorials I had such a case when I had to create a list of all HTML files in folders and subfolders. There are several ways to create a list of all files in 'Directory' or 'Subdirectories'.

On other cases using various programs, I wrote in tutorials on this link List of all folders and files in a directory. However, here is about Excel and how to use VBA or formulas to create a list of all folders, file names, their paths, file size, file type, file creation date, file modification date, last access date, and so on.

In this Excel tutorial with examples of VBA macros, I have shown different variants of how you can display a list of all or missing paths in the folder or subfolders. All the credit for collecting VBA macros, belong to the authors of the same.

How to create a list of all files from directories and subdirectories

In the image below, notice which data is returned to the VBA procedure (Image 1a).

  1. File Name
  2. File Size
  3. File Type
  4. Date Created
  5. Date Last Accessed
  6. Date Last Modified

Image 1

List all files name in the directory and subdirectories

To make the VBA macros work properly you must include Microsoft Scripting Runtime. In the picture below, see how to include this Reference.

To include Microsoft Scripting Runtime do the following:

  1. Click Tools on the text menu
  2. Select 'References ...' in the drop-down menu
  3. In the newly opened dialog box, set ON to the box before 'Microsoft Scripting Runtime'.
  4. Click the OK button

Image 1a

Microsoft Scripting Runtime

The VBA macro that gives the results shown in the first picture (Image 1) is the following. For this first example we have two VBA procedures that are related to each other. Copy these two procedures below or VBA macros into your VBE (Visual Basic Editor) Module for Excel.

Sub ListAllFilesFromFolderAndSubfolder()
'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
'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"
strTopFolderName = "C:\Temp\" 'Change the top folder to a variable
'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
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

How to create a list, path to folders and subfolders, as well as file name with extension

In this second example, the UDF function creates a list of all file names and paths as well as the size of each file in the Folder or Subfolder. Copy this UDF function under the Image2 into the Module, your VBE for Excel. On the worksheet where you want to create a list with titles in the columns, type the titles: Path, File name and Size. Workbook where you placed these two procedures below, make sure to place the top folder from which you want to return the list of all files to their paths and file sizes.

These VBA macros below will return the following data:

  1. Path
  2. File Name
  3. Size

Also, do the following steps:

  1. Select the range of cells in which you expect the list. Note that you need to select the number of rows as you expect to be the number of files. It is best to select a larger number of Rows than the expected number of files. (eg Select the 'A2:C50' cell range.
  2. Press the F2 key (the first cell of the selected range is now active)
  3. Enter this CSE formula =ListFiles("C:\Temp") in the first cell of the selected range. This formula activates the UDF function, so your Workbook should be captured in * .xls or * .xlsm or * .xlsb format. Of course you can set the other main folder from which you want to return a list of all files.
  4. Press CTRL+SHIFT+ENTER at one time
  5. Now you have a list of all files and their path

Image 2

Show a list of all files and folders in the master folder using Excel UDF

VBA macros for UDF function and a list of all files with their paths. These two VBA procedures below are related to each other and work in pairs. This way of using UDFs, returns a list of folders and subfolders in which is located Workbook which contains these two procedures.

Public Tmp() As String
Function ListFiles(FolderPath As String)
Dim k As Long, i As Long
ReDim Tmp(2, 0)
If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
End If
Recursive FolderPath
k = Range(Application.Caller.Address).Rows.Count
If k < UBound(Tmp, 2) Then
MsgBox "There are more rows, extend user defined function"
Else
For i = UBound(Tmp, 2) To k
ReDim Preserve Tmp(UBound(Tmp, 1), i)
Tmp(0, i) = ""
Tmp(1, i) = ""
Tmp(2, i) = ""
Next i
End If
ListFiles = Application.Transpose(Tmp)
ReDim Tmp(0)
End Function

Function Recursive(FolderPath As String)
Dim Value As String, Folders() As String
Dim Folder As Variant, a As Long
ReDim Folders(0)
If Right(FolderPath, 2) = "\\" Then Exit Function
Value = Dir(FolderPath, &H1F)
Do Until Value = ""
If Value = "." Or Value = ".." Then
Else
If GetAttr(FolderPath & Value) = 16 Then
Folders(UBound(Folders)) = Value
ReDim Preserve Folders(UBound(Folders) + 1)
Else
Tmp(0, UBound(Tmp, 2)) = FolderPath
Tmp(1, UBound(Tmp, 2)) = Value
Tmp(2, UBound(Tmp, 2)) = FileLen(FolderPath & Value)
ReDim Preserve Tmp(UBound(Tmp, 1), UBound(Tmp, 2) + 1)
End If
End If
Value = Dir
Loop
For Each Folder In Folders
Recursive FolderPath & Folder & "\"
Next Folder
End Function

How to make a list of all file names and full paths to folders and subfolders

This third example is different from the two previous examples. On the worksheet, you set up the path to the main folder from which you want to return a list of all named files, and the path to the desired files. You also have the option to choose if you want to create a list of all files only in the main folder or include subfolders (see image below). Notice in the image below that the first row in which to display a list of the 'line 5'. This command line is placed in the VBA macros. Also note that the conditions are in 'B1' and 'B2' cells. The workbook that contains these VBA procedures does not have to be located in the same folder from which we drag the file list.

  • B1 cell -> Enter th path to main folder
  • B2 cell -> Condition TRUE = Includes Top folder and subfolder
  • B2 cell -> Condition FALSE = Only Top folder

Slika 3

Displays a list of all the files and all paths for specific main folder

The VBA procedures that return the result from Image3 are as follows. These two VBA procedures below are related to each other and work in pairs. Copy these into the VBE for Excel module.

Dim iRow
Sub ListPathNameFiles()
iRow = 5 'start first row where will be list
Call ListMyFiles(Range("B1"), Range("B2")) 'where are placed the conditions
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 1
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub

Displays the file name and sub-folder names in the main folder with the full path to the file

In this fourth example of VBA macros shown below (Image4) returns a list of names of all files with extensions and sub directory names as well as additional information.

  • File Name,
  • Size,
  • Modified Date,
  • Last Accessed,
  • Created Date,
  • Full Path,
  • Size

Note that in this example, VBA macros do not return a list of files from the subfolder, they only create a list of files name and sub-folders located in the main 'Top folder'.

Image 4

List all file names and subfolders names in the specific master folder

In this example, there is a package of VBA macros. So you can see that there are two VBA procedures and one UDF function. All of these VBA procedures are connected and one without the other is not working properly.

Sub DirectoryListAllFiles()
' variables
Dim objFSO As Object
Dim objFolder As Object
Dim objElement As Object
Dim objFile As Object
Dim strSourceFolder As String
Dim lngRow As Long
ToggleStuff False
strSourceFolder = BrowseForFolder 'call up the browse for folder routine
If strSourceFolder = "" Then Exit Sub
Cells.Select
Selection.ClearContents
With Range("A1:F1")
.Value = Array("File", "Size", "Modified Date", "Last Accessed", "Created Date", "Full Path", "Size")
.Interior.ColorIndex = 6 'cell background of the column header
.Font.Bold = True
.Font.Size = 12
End With
lngRow = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strSourceFolder)
For Each objElement In objFolder.SubFolders
Cells(lngRow, 1).Value = objElement.Name
'Cells(lngRow, 2).Value = Format(objElement.Size, "0,000") & " KB"
Cells(lngRow, 3).Value = objElement.DateLastModified
Cells(lngRow, 4).Value = objElement.DateLastAccessed
Cells(lngRow, 5).Value = objElement.DateCreated
Cells(lngRow, 6).Value = objElement.Path
lngRow = lngRow + 1
Next
For Each objElement In objFolder.Files
Cells(lngRow, 1).Value = objElement.Name
Cells(lngRow, 2).Value = Format(objElement.Size, "0,000") & " KB"
Cells(lngRow, 3).Value = objElement.DateLastModified
Cells(lngRow, 4).Value = objElement.DateLastAccessed
Cells(lngRow, 5).Value = objElement.DateCreated
Cells(lngRow, 6).Value = objElement.Path
lngRow = lngRow + 1
Next
Columns("A:F").AutoFit
Set objElement = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
ToggleStuff True 'turn events back on
End Sub

Sub ToggleStuff(ByVal x As Boolean)
Application.ScreenUpdating = x
Application.EnableEvents = x
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'VBAExpress.com..portion of Knowledge base submission
'codeguru.com
Dim ShellApp As Object
ReStart:
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
MsgBox "Not a valid folder"
GoTo ReStart
End Function

List all files in the directory using only Excel formulas

In this fifth example of this Excel tutorial, I will show that using Excel formulas, we can display a list of all file names in the 'Top folder' where the Workbook is located, where we want to return the list of all file names.

So Workbook should be placed in the desired folder, copy the formula listed below appear in the respective workbook and you will have a list of file names that are found in the respective specified folder. However, before you copy the displayed Excel formulas, you need to name the help formula in Excel Name Manager.

Image 5

Appointment of a formula in Excel Name Manager

At the 'Image 5' above you can see the result of the named auxiliary formula. If you are not clear, see the detailed instructions on how to name the formula in Name Manager.

So, enter this formula below in Name Manager (Excel will automatically add the name of the worksheet where you set the formula).

  • Name: FileNameList
  • Refers to: =FILES($A$1)

In the 'A1' cell, set the following formula:
=SUBSTITUTE(CELL("filename");RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("@";SUBSTITUTE(CELL("filename");"\";"@";LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename");"\";"")));1));"*")

In the 'C2' cell, place the following formula (copy the formula down to the desired last row)
=IFERROR(INDEX(FileNameList;ROWS($A$1:A1));"")

You now have a list of all file names that are in the same folder as the Workbook that you set up the specified formulas. Workbook must be saved in *.xls or *.xlsm or *.xlsb format.

Image 5a

List all file names in the folder where the Workbook is located

I hope this collection of VBA macros and UDF functions, shown in this Excel exmple tutorial, will help you create a list of all file names and paths that are in the folder and subfolder.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.