List of all Files in a Folder and its Subfolders

Written by

How to create a list of all filenames inside a specified folder and its subfolders including additional information like file types, sizes, dates and path lengths in Microsoft Excel

From time to time, I am taking the liberty to post something totally off topic (see here or here), i.e. something that has nothing to do with data analysis and data visualization.

Today’s short article belongs to this category.

A couple of weeks ago, I ran into a problem with my offline backup software. The application threw a few errors, reporting it could not backup a couple of my documents. The issue was easy to find: the path of those files exceeded the maximum path length of the Windows API (260 characters).

It wasn’t so easy to fix, though, because the error log file of my backup software isn’t very helpful. I needed something to easily identify the files with a path length exceeding the 260 characters limitation, so I could shorten the folder and file names.

Hence, I wrote a little tool which automatically creates a list of all files inside a specified folder and all its subfolders, including the file names, the paths, the file types, the dates (created, last modified, last accessed) and the lengths of the path and filename. This list sorted descending by path lengths made it easy to identify the files my backup software couldn’t handle.

According to this Microsoft article in the Windows Developer Center, the maximum length limitation shall disappear in Windows 10, version 1607. The root cause for creating this workbook and code may go away soon, but I assume the little tool of easily getting a list of filenames inside a specified folder may be helpful on other occasions, too.

If you are interested, here it is for free download:

Download retrieve filenames from folder and subfolders (zipped Excel 2010 – 2016 workbook, 34.2K)

Download, unzip and open the tool, enable macros, click on the import icon at the top of the sheet, select a folder and wait until the code is finished (the status bar at bottom left shows the progress).

Please be advised that the code is not optimized for performance. Importing the ~41,000 files in my document folder took ~6 minutes on my machine. Not really fast, but since I am not doing this on a daily basis, it is good enough in my book.

I hope this will be helpful for someone else, too.

More posts on data analysis and visualization will come soon.

Stay tuned.

excel list of filenames file information in excel file list get filenames in excel get list of files in a folder list of files list of files in a folder and subfolders

Comments

14 responses to “List of all Files in a Folder and its Subfolders”

August 30, 2016

Julian

“dir /b /a-d /s” in the command prompt will also do the trick. You can pipe it to a txt file by appending “> f.txt”, after which you can import into Excel and do what you need to do.
You can modify the parameters to include other things, but for the purposes you described I imagine it would have been enough.
A little more post processing, of course, but a little easier than writing a macro! A lot faster than 6 minutes too.

Reply
August 30, 2016

Robert

Julian,
you are right, using the command prompt, DIR and piping the results into a text file is another option. Thanks for pointing this out.
Well, there is always more than one way to skin the cat, right?
I am wondering, though, why Microsoft does not provide this option on the Ribbon of the Windows Explorer, but forces us to type in cryptic DOS commands as if we would still live in 1985…

Reply
December 15, 2016

Thomas k

I would suggest using “transform and get” to import the list of files.

Reply
December 15, 2016

Robert

Thomas,
I gave that a try, too, but it didn’t work for me. It takes only a few clicks to set up the query, but I never managed to get the entire job done. Loading the data took forever and always stopped with a “ran out of memory” error after a couple of thousand records.
Anyway, thanks for the suggestion.

Reply
March 31, 2017

Rolf

Hello Robert,
here is a nice little batch file which produces a txt file containing only those files exceeding a given path length:
echo off
:: Start 250_characters.bat with source path like 250_characters “e:”
for /F “tokens=*” %%i in (‘dir %1\ /b /s’) do call :loop %%i
goto end
:loop
set Fullpath=%*
if (“%Fullpath:~250%”) GTR (“”) echo %fullpath% >>e:\250_characters.txt
:end
Fast and elegant (not by me, stumbled over it years ago).
Greetings Rolf (thank you again for NRW maps!)

Reply
May 9, 2017

Robert Sterbal

This really isn’t a cryptic dos command. Because the copy from the ribbon includes properties, is isn’t as functional as the log from the dir command. Here is a good resource for those command lines you want to learn more about: https://ss64.com/nt/dir.html

Reply
May 9, 2017

Robert

Robert,
maybe it was unfair to call it cryptic, because it is documented. However, it still is a DOS command you have to enter in the command prompt and this procedure is everything else than user-friendly for the not unusual request of producing a list of all files inside a folder and its subfolders.
What do you mean by “the copy from the ribbon”, by the way?

Reply
May 12, 2017

Marko

Nice tools, is possible to change a little bit code and exchange first column or second in hyperlinks? it could be usefull in work.
Greetings from Poland 🙂

Reply
May 12, 2017

Robert

Marko,
this is possible. You would have to add a For Next loop to the end of the code (after the data was inserted into the table), looping through all rows in the table and use the Hyperlinks.Add method to add the value of the second column (the path) as the hyperlink to the cells. Have a look here how the .Add method works:

Hyperlinks.Add Method (Excel)

Reply
July 3, 2017

Larry Salvucci

Robert, Is there a way to modify your code to only pull specific types of files? Such as any type of excel file?

Reply
July 3, 2017

Robert

Larry,
absolutely. I can think of several solutions:
One way would be to change all subs/functions to check if the filetype (objFile.Type) equals the desired filetype. Probably the most elegant and fastest approach, but you have to adjust all functions and some of them would become more complicated (e.g. CountFiles).
Another solution would be to change only the main sub: first let the code create a list of all files (as it is now), define another VBA array, loop through the array with all files and transfer only those to the new array which have the desired filetype. Finally write this new array to the worksheet. Probably a bit slower than the first approach, but easier to implement, I guess.

Reply
July 25, 2017

Govardhan

Dear,
If possible can you please include Owner/Author Column in this file?
Thanks a TON!

Reply
July 25, 2017

Robert

Govardhan,
your request sounds easier than it is. The code in my tool uses the VBA File Object. What you are referring to (author and owner) are Microsoft Office built-in document properties. That means, the VBA File Object does not have these properties and you can’t easily add them to the list the tool provides as an output.
To meet your requirement, you would have to considerably enhance the code to check if the currently processed file is a Microsoft Office file (Excel workbook, Word document, etc.), if so, read the required attributes from the built-in document properties and add them to the table.
Not impossible, but quite some extra coding necessary.

Reply
June 21, 2018

Kot

This is a fantastic tool. Saved me a lot of time. Thank you so much!

Reply