To generate a list of all files in a defined folder, perform the following steps:
- Create a folder reference in a cell, such as the following in cell B3 on a sheet named Sheet1:
- Select Formulas (tab) -> Defined Names (group) -> Name Manager
- In the Name Manager dialog box, click “New…”
- In the New Name dialog box, we will name the reference “GetFiles” and reference the FILES macro by entering “=FILES(Sheet1!$B$3)” in the “Refers to” field.
- Click OK to close the Name Manager dialog box.
If we select a cell and type the formula…
…it fails with a #N/A error message.
The reason this fails is that we need to supply the FILES macro with search parameters.
If we wish to list all files in a folder, we can add a “slash-asterisk” to the end of the path pointer in cell B3.
The asterisk is a wildcard that denotes “any file”.
To get the list to read vertically, we wrap the formula in a TRANSPOSE function.
NOTE: Remember to press CTRL-ALT-F9 to update the list.
If we want to see the results in sorted order, wrap the formula in a SORT function.
If we wish to only see Excel workbooks, we can update the search parameter to “\*xlsx”.