I’m currently working on a project where I need to analyse documents so they can be properly classified and uploaded to a SharePoint based document management system. Looking at the folder structure with Windows Explorer does not really help much. I need a list of files in their respective locations, so I can sort and filter, pivot and drill down.

There are some VBA code snippets out there which create reports of files in folders and sub folders in Excel, but none of them really do what I need, so I listed out my requirements and re-wrote some code originally posted by Leith Ross at Excelforum.com.

My requirements:

• Specify a starting folder
• Have all files and all sub folders listed out, or just the starting folder
• Show starting folder in one column and the sub folders in separate columns each, so I can create pivot tables, sort and filter on the folder location
• Show file name, file extension, file size, and core dates in separate columns, so the results can be sorted and filtered
• Highlight large files with conditional formatting in three colours for > 2MB (yellow), >5MB (orange) and >10MB (red)
• Keep a column with the original sort order (=location of the files)
• Enable unlimited levels of nesting
• Create the report in a new file, so I don’t have to clean up the original

I created an Excel table with these columns:

Order
File name
Extension
Created
Modified
Size
Size MB
Path
Level1
Level2
… through to Level20
The table has one row of dummy data in which conditional formatting and one formula for the conversion from Byte to MB is defined. (Yes, I know I could do the conversion with VBA, as well).

Another sheet contains the user interface. It has just one big button to start the macro, which then

• prompts the user for a starting location, which must start with a drive letter in the format “Z:” and can contain a longer path.
• checks that the second character of the location is a colon. If not, it cannot be a valid drive location.
• prompts the user whether or not to include sub folders (just in case you only want to list out one folder)
• goes off to work and gathers all the information, writes it into the table, splitting out the data into the columns
• provides a status bar message with the folder that is currently in progress (the macro can run for quite a long time, depending on the number of files returned)
• copies the report to a new workbook and removes the placeholder row in the table
• closes the macro enabled file without saving, so nothing needs to be cleaned up.

If a folder has more than 20 nested sub folders, the macro will still work and write the folder names into the next available column, but the header of the table will have to be added manually.

The file can be downloaded here: CreateFileReport.xlsm

If you want to make changes to the original, make sure to save the workbook before you run the macro, or edit the code and comment out the row that closes the original workbook without saving.

Enjoy.