List files in a folder

Sometimes it is necessary to get a list of files in a given folder and its subfolders on an Excel sheet. This has happened many times in my experience, for example:

• list in the appendix to the training contract a list of files from handouts for especially scrupulous lawyers in some companies
• create a list of files for the project specification
• compare the contents of folders (original and backup, for example)

Several methods can be used to accomplish this task.

Method 1. Skeleton from the closet – the FILES function

This method uses the ancient function Files (FILES), left in Microsoft Excel since the distant nineties. You will not find this function in the general list of functions, but for compatibility, it is still inside the Excel engine, and we can use it.

The mechanism is:

1. In any cell of the sheet (for example, in A1) we will enter the path to the folder, the list of files from which we want to get.

Note that the path must end with an asterisk pattern:

• *. * – any files
• *.xlsx – Excel workbooks (xlsx extension only)
• *.xl* – any Excel files
• *report* – files containing the word report In the title

etc.

2. Create a Named Range Using a Tab formula – next button Name Manager – Create (Formulas — Names Manger — Create). In the window that opens, enter any name without spaces (for example My files) and in the range field the expression:

=FILES(Sheet1!$A$1)

After clicking on OK a named range will be created with the name My files, where the list of all files from the folder specified in A1 is stored. It remains only to extract them from there.

3. To extract the names of individual files from the created variable, use the function INDEX (INDEX), which in Excel pulls data from an array by their number:

If it’s lazy to make a separate column with numbering, then you can use a crutch in the form of a function LINE (ROWS), which will count the number of filled rows from the beginning of the list automatically:

=INDEX(My_files; ROWS($B$3:B3))

Well, hide the errors #LINK! at the end of the list (if you stretch the formula with a margin) you can use the standard function IFERROR (IFERROR):

=IFERROR(INDEX(My_files; LINES($B$3:B3)); «»)

Important note: formally a function Files refers to macro functions, so you will need to save your file in a macro-enabled format (xlsm or xlsb).

Method 2. Ready-made macro for the lazy

If you are familiar with macros (not in the sense of programming them, but in the sense of copy-pasting ready-made codes in VBA), then a small macro that adds a new empty sheet to the current workbook and displays a list of all files with their parameters from user-specified folder.

To add a macro to your workbook, press the keyboard shortcut Alt+F11, or button Visual Basic tab developer (Developer), in the Visual Basic editor window that opens, insert a new module through the menu Insert – Module and copy the text of this macro there:

Sub FileList() Dim V As String Dim BrowseFolder As String 'open the folder selection dialog box With Application.FileDialog(msoFileDialogFolderPicker) .Title = "List files in a folder" .Show On Error Resume Next Err.Clear V = .SelectedItems(1) If Err.Number <> 0 Then MsgBox "You didn't select anything!" Exit Sub End If End With BrowseFolder = CStr(V) 'Add a sheet and display the table header ActiveWorkbook.Sheets.Add With Range("A1:E1") .Font.Bold = True .Font.Size = 12 End With Range ("A1").Value = "File name" Range("B1").Value = "Path" Range("C1").Value = "Size" Range("D1").Value = "Date created" Range ("E1").Value = "Date Modified" 'call file list display procedure 'change True to False if you don't want to display files from subfolders ListFilesInFolder BrowseFolder, True End Sub Private Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean) Dim FSO As Object Dim SourceFolder As Object Dim SubFolder As Object Dim FileItem As Object Dim r As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set SourceFolder = FSO.getfolder(SourceFolderName) r = Range("A65536") .End(xlUp).Row + 1 'Find the first empty row 'Display file data For Each FileItem In SourceFolder.Files Cells(r, 1).Formula = FileItem.Name Cells(r, 2).Form ula = FileItem.Path Cells(r, 3).Formula = FileItem.Size Cells(r, 4).Formula = FileItem.DateCreated Cells(r, 5).Formula = FileItem.DateLastModified r = r + 1 X = SourceFolder. Path Next FileItem 'call the procedure again for each subfolder If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Columns("A:E").AutoFit Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub  

To run a macro, press the keyboard shortcut Alt+F8,or button Macros (Macros) tab developer (Developer), choose our macro Filelist And click the Run (run). In the dialog box, select any folder or drive and – voila!

If you want to display a live hyperlink instead of the file path in column B, then replace the 52nd line

Cells(r, 2).Formula = FileItem.Path

on

Cells(r, 2).Formula = «=HYPERLINK(«»» & FileItem.Path & «»»)»

Method 3: Power and beauty – Power Query add-in

Power Query is a very powerful yet free Excel add-in from Microsoft that simplifies many data loading and transformation tasks. In our situation, it can also help a lot.

If you have Excel 2016 or later, then Power Query is already built into Excel by default, so just on the tab Data select team Create query / Get data – From file – From folder (Create Query / Get Data — From file — From folder). If you have Excel 2010-2013, then Power Query will need to be downloaded from the Microsoft website and installed as a separate add-in, and it will appear in your Excel as a separate Power Query tab. It will have a similar button. From file – From folder (From file — From folder).

In the window that opens, you will need to specify the folder whose contents we want to receive. After clicking on OK Power Query will search the specified folder and all nested subfolders and display a window with a preview of the results:

If the appearance of the list suits you, then you can safely press the button below Download (Load)to fill this data into a new sheet. If you want to further process the list (delete unnecessary columns, select only the necessary files, etc.), then you need to select the command Edit / Transform Data (Edit / Transform Data).

A Power Query editor window will open on top of the Excel window, where we will see a list of all our files in the form of a table:

Next, there are several options:

• If you only need files of a certain type, then you can easily select them using the filter by column Extension:
• Similarly, filters by columns Date accessed, Date modified or Date created you can select files for the desired period (for example, created only in the last month, etc.):
• If you need to get data from not all folders, then filter by column Folder Pathto leave only those lines where the path contains/does not contain the desired folder names:
• There you can also sort the files by any column, if required.
  

After the necessary files are selected, you can safely delete unnecessary columns by right-clicking on the column heading and selecting the command Remove (Remove column). This, by the way, will not affect the filtering or sorting of our list in any way:

If in the future it is planned to count the number of files in each folder (for example, to control received requests or calculate statistics on requests), then it makes sense to do a couple of additional actions:

• Right click on a column Folder Path and select the command Duplicate column (Duplicate Column).
• Select the copied column and on the tab Transformation (Transform) select Split Column – By Delimiter (Split Column — By delimiter)

We will get a few more columns next to our data, where the names of nested folders will be duplicated – this will come in handy a little later for calculating statistics using a pivot table:

The resulting columns can be renamed (Диск, Folder1, Folder2 etc.) by simply double-clicking on the title of each.

And finally, when the list is ready, it can be uploaded to the sheet using the command Home — Close and Load — Close and Load in… (Home — Close & Load — Close & Load to…):

As an added bonus, you can make another column with the function HYPERLINK (HYPERLINK), which will create beautiful link arrows to instantly jump to each file:

A trifle, but nice 🙂

And it is doubly pleasant that in the future, when changing the contents of the source folder, it will be enough just to click on our table and select the command Update & Save (Refresh) – and Power Query will perform the entire chain of actions programmed by us once already automatically, displaying all changes in the folder.

• What is a macro, where to insert macro code in Visual Basic
• Create backup copies of valuable files
• What is Power Query and what can you do with it