Way back, when Excel 4.0 was around, it used a macro language called XLM. VBA was introduced in Excel 5.0.
These XLM macros are still in Excel and you can use them to do certain cool things. One of these macros is FILES.
Now I call these macros, but when you use them you just use them like any function in your worksheet. You don't have to do any kind of programming.
The neat trick with FILES though is how you combine it with a defined name, I first saw this over at Jan-Karel Pieterse's blog. Let's see how it works.
Listing Files in a Folder
Using FILES we can create a list of the files in a folder, but these are not hyperlinked so clicking on the file name won't open the file.
To start, let's enter into A1 the full path and search string we want to use to list our files. You can use wildcards here e.g.
Next we define a name, so open Name Manager (CTRL + F3) and create a new name called MyFiles.
In the Refers to box enter =FILES($A$1), then click OK.
You should now have a new name like so
Close the Name Manager.
Now for the fun part. In B1 enter the formula =INDEX(MyFiles,ROW()), hit Enter and the first file that matches your search parameters will be listed.
Copy this formula down the column and each file in the folder will be listed in turn until you end up with #REF! errors indicating no more files to be listed.
You can edit the search parameters in A1, and the file list will automatically update.
To remove these unsightly errors you can wrap the formula in column B in an IFERROR e.g. =IFERROR(INDEX(MyFiles,ROW()),"-----"), so when there are no more files left, you get ----- displayed which is more visually appealing than #REF!. You can put whatever string you like in here of course.
The workbook must be saved as a macro enabled workbook, even though you haven't actually written any VBA code.
I haven't figured out a way to work out how many files are in the folder, which would tell me how many rows I need to copy the formula down to. If you don't use enough rows you risk missing some files, so just use lots and lots if you're not sure 🙂
Download Sample Workbook
You can download a sample workbook with the name and formula set up.