Microsoft Office Tutorials and References
In Depth Information
Tip 8: Generating a List of Filenames
Generating a List of Filenames
This tip describes how to retrieve a list of filenames 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 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 (see Figure 8-1):
4. Click OK to close the New Name dialog box.
Figure 8-1: Using the New Name dialog box to create a named formula.
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:
Then this formula displays the first file found:
If you change the second argument to 2, it displays the second file found, and so on.