Generating a List of File Names

This tip describes how to retrieve a list of file names in a folder and display them in a 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 file names 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 Tab ➜ Define Name to display the New Name dialog box (CTRL + F3).
2. Type FileList in the Name field.
3. Enter the 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 file names 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:

• F:\Future in Hands\Training\*  to display all files
• F:\Future in Hands\Training\*xl* to display all Excel files

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.

Below figure 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. When you change the directory in cell A1, the formulas update to display the new file names.