How to Retrieve A List of Filenames in A Folder and Display Them in A Excel Worksheet

This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of filenames into a worksheet — something that normally requires a complex VBA macro. Start with a new workbook and then follow these steps to create a named formula:

1. Choose Formulas➜Define Name to display the New Name dialog box.

2. Type FileList in the Name field.

3. Enter the following formula in the Refers To field :

     =FILES(Sheet1!$A$1)

4. Click OK to close the New Name dialog box.

Note that the FILES function is not a normal worksheet function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of filenames in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1. For example:

E:\Backup\Excel\*.xl*

Then this formula displays the first file found:

=INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Figure above shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column:

=INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. Note that cell A21 (and cells below it) displays an error. That’s because the directory has only 19 files, and the formula is attempting to display files that don’t exist. When you change the directory or filespec in cell A1, the formulas update to display the new filenames.

Note : If you use this technique, you must save the workbook as a macro-enabled file (with an .xlsm or .xls extension).