In this post we’re going to explore how to go about getting a list of file names in a folder without using any VBA code. This method uses the little known / often forgotten 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 we’re going to use one of these in a named function.
Create a named function.
- Select cell A1.
- Go to Formula tab in the ribbon.
- Select Define Name from the Defined Names section.
- Type in List_Of_Names in the Name area.
- Type in =FILES(Sheet1!$A$1) in the Refers to area.
- Press the OK button.
Specify the folder path.
- 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
- Enter the formula =INDEX(List_Of_Names,ROW(A1)) into any cell.
- Copy and paste the formula down until you see a #REF! error. This means we’ve reached the last file in the folder.
Not too difficult and no VBA needed.
This was the best tip I have ever read…thank you so much.
Is it possible to do the same thing with a list of folders?
I tried using “=FOLDERS(Sheet1!$A$1)” but doesn’t work.
Regardless, this tip has revolutionised my workbooks!