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
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, 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.