Generate list of files in a folder on a worksheet
More fun tonight with Excel 4 macros. To generate a list of all of the files in a folder directly on a worksheet follow these steps.
1. In cell A1 enter the path for the folder. Add *.* to get a list of all files, or *. followed by a file extension to only get files in a particular format.

2. Press CTRL + F3 to open the name manager. Enter a new defined name called, 'FileList' and have it refer to: =FILES(Sheet1!$A$1)

3. In cell B1 enter this formula: =INDEX(FileList,ROW()) and then pull it down using CTRL+ D. Excel will generate a list of all of the files in the named folder.

Thanks to Phil Treacy for the Excel 4 Macro fun!