Have you had to work with a folder containing lots of documents, and you wanted to get the list of all the documents? This may be a directory listing all the policy documents in the organization, or the CVs of job candidates, or PDF forms filled by several employees. If you needed to get a quick list of all items in a directory, this is the hack for you.

Here’s the code that makes this work. It’s a short 6-line VBA:

Sub ListFiles()

F = Dir("C:\Users\Omowunmi\Desktop\Ivanov 2019\*")

Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop

End Sub

This is the meaning of the code above:

1. The first and last lines (Sub xxx() and End Sub) are the standard start and end commands for every VBA code. What follows Sub … is the name of the VBA which will show up when you want to run it using the keyboard shortcut (Alt + F8). I’ve chosen to name my VBA code “ListFiles”.
2. The second line of the code assigns all the files within a specific directory (in my example, the directory named “Ivanov 2019” on my Desktop folder) to a variable named “F”. You can use any variable name other than F, as long as it meets the naming convention for VBA variables. To get the full path of the directory – which is “C:\Users\Omowunmi\Desktop\Ivanov 2019” in my example, right-click within the folder to bring up the Properties. Or click the name bar to reveal the full path name, and copy it from there.
3. The next 5 lines are a Do While Loop that asks the code to return the name of each file within the stated directory into the selected cell, stopping to move to the next row (the Offset line) before returning the next file name.

Points of note:

The code starts to return the file names from the actively selected cell, so ensure you have selected an empty column before you run the code, to avoid overwriting the contents in your Excel sheet.

You can modify this code to extract only files with a specific file extension e.g. only PDF files, Excel worksheets or Word documents. To do this, you will simply adjust the second line of the code as shown below:

To extract only PDF files | F = Dir(“C:\Users\Omowunmi\Desktop\Ivanov 2019\*.pdf”)

To extract only XLS files | F = Dir(“C:\Users\Omowunmi\Desktop\Ivanov 2019\*.xls”)

To extract only DOC files | F = Dir(“C:\Users\Omowunmi\Desktop\Ivanov 2019\*.doc”)