How To Generate A List Of File Names From A Folder Without VBA

2016-11-16

Get The Completed Workbook

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.

 

Step-001-How-To-Generate-A-List-Of-File-Names-From-A-Folder-Without-VBA How To Generate A List Of File Names From A Folder Without VBA

 

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.

 

Step-002-How-To-Generate-A-List-Of-File-Names-From-A-Folder-Without-VBA How To Generate A List Of File Names From A Folder Without VBA

 

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.

 

Not too difficult and no VBA needed.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

read more
25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

read more

Comments

1 Comment

  1. Vic

    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!

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This