There is a particular folder on my hard drive with several dozen files in it. I wish I had a list of those files so that I could email them, or create an exhibit list, etc.
The following routine will make Excel spit out all those filenames in a snap:
Step 1: Select cell A1 in a new worksheet and go the Formulas Tab. Select the button called Name Manager:
Under Name, type MyFiles. The “Refers to” box should already contain “Sheet1!$A$1” if your cursor is in cell A1. Put this in parentheses and type =FILES in front of it, like so:
Step 2: Copy the folder address you want to index and paste it into cell A1.
Also, add \* at the end of the file path:
MyFiles is now pointing to the file path in cell A1.
Step 3: Put the following function in cell A3:
The INDEX formula tells Excel to pull filenames from MyFiles, which is the folder referenced in A1. The formula in A3 pulls the first value because the current row is 3, so ROW()-2 =1.
Step 4: Copy the formula down several rows. Notice that the formula looks exactly the same in each row, but since the ROW() function evaluates the current row number, each new formula pulls the next filename.
The results will look like this:
Step 5: Copy the formula down about as many rows as there are files in the folder, until you begin to get error messages. That will mean that you’ve reached the end of the files:
Save the workbook with a snappy name like “Excel Esquire Directory Listing Miracle File.xlsx”
Here’s a few additional tips to make the most of this routine:
Tip 1: You can adjust the wildcard in cell A1 to pull only filenames with certain characteristics. For example, putting *.doc* at the end would pull only the files with a .doc or .docx extension.
I want to pull the documents that have the word Park in them, so I adjust the wildcard to read \*Park*, with the following results:
Tip 2: You can change the file path in cell A1 whenever you want and generate a new list of files.
Tip 3: This routine does not pull the filenames from subfolders in the target folder. You would have to repeat this operation for each subfolder if you wanted to extract those files.
Tip 4: If you recoil at the sight of error messages, try wrapping the INDEX formula in the warm embrace of IFERROR to generate a friendlier message:
=IFERROR(INDEX(MyFiles,ROW()-2,”Congratulations! You’ve reached the end!”)
In a future post I’ll explain how to create a hyperlinked document list—in such a list you can click on the document name in a list, and the document will launch automatically.
Click the Follow button to stay tuned, and to learn about CLE-accredited Excel workshops!
Seems to work on a new file I created. But when I try to place it into another existing file (*.xlsm), it only generates errors. I recreated it in the existing file, but can’t generate a file list. I can in an xlsx file
Perhaps you are forgetting to add “\*” after the file path in A1? That would lead to #N/A errors. Or forgetting to put =FILES in front of the cell reference in your defined name?
Thanks for your comment Bruce. It’s true that you need to save the file as a macro-enabled (.xlsm) file, but I’m not sure why you’re getting error messages. I’ll investigate and get back to you.
Interesting FILES function. May I know what the function do?
Pingback: Get Excel to Automatically Launch Documents from an Exhibit List, Privilege Log, or Hot Documents List | Excel Esquire
In order to update the list, I have to backspace the asterisk and add it back in, is there a way to have the list update automatically when I open the spreadsheet that contains this formula?
I have another question for you…I am trying to get cell data from each file of the list of files this formula creates, nothing seems to work.