Microsoft Office Tutorials and References
In Depth Information
The FILES function is not a normal worksheet function. Rather, it's an old XLM style macro function that is in-
tended to be used on a special macro sheet. This function takes one argument (a directory path and file specific-
ation) 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.
Figure 3-26 shows an example. The path and filespec is in cell A1. Cell A2 contains this formula, copied down
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 A22 displays an error because the dir-
ectory has only 20 files, and it's attempting to display the 21st file.
When you change the directory or filespec in cell A1, the formulas update to display the new filenames.