This post is going to explore how to get a list of file names in a folder without using any VBA code.

This method uses the legacy Excel 4 XLM functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc.

These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick, you will need to create a named function.

Use the above link to download the example workbook from this post.

Create a named function.

1. Select cell A1.
2. Go to Formula tab in the ribbon.
3. Select Define Name from the Defined Names section.
4. Type in List_Of_Names in the Name area.
5. Type in =FILES(Sheet1!$A$1) in the Refers to area.
6. Press the OK button.

Specify the folder path.

1. Enter the path of the folder containing the files that you want to get the names of into cell A1. In this example my files are in C:\Example.
   • If I wanted all files I would enter C:\Example\* into A1
   • If I wanted all .xlsx files I would enter C:\Example\*.xlsx into A1
   • If I wanted all Excel files files I would enter C:\Example\*.xls* into A1
2. Enter the formula =INDEX(List_Of_Names,ROW(A1)) into any cell.
3. Copy and paste the formula down until you see a #REF! error. This means we’ve reached the last file in the folder.

This is an easy way to list all the files in a given folder and no VBA is needed.

A word of caution is needed. Since these XLM 4 functions are a legacy feature in Excel, they are not supported and shouldn’t be relied upon for anything critical.