Filter by Categories
Select all
Budget
Business Statements
Calendars
Federal Income Tax
Gantt Chart
GST India
Health, Logs and Personal Trackers
Inventory
Investments
Invoice/ Bill
Maintenance
Payroll & HR
Project Reports & Timelines
Purchase Order/Requisition
Quotation
Rental Ledger
Risk Registers
Sales/ Marketing
SWOT Analysis
Timesheet/ Attendance
UAE VAT
UK VAT
Filter by File Types






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 feature image

How can files names from a folder be useful ?

Often, while working efficiently – you will require to simulate a list of file names from a folder and using Microsoft Excel you can do it easily. These computation can help you in data analytics and file indexing effectively. With this tutorial, you can learn to do this without using Visual Basic Studio in Microsoft Excel. Often, people find using VBA codes hectic- hence this tutorial can be one important tutorial for you.

Tutorial to generate file names from a folder

In this post you are going to learn how to get a list of file names in a folder without using any VBA code in Microsoft Excel. 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.

  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.

Not too difficult and no VBA needed.

Content

Macro Codes in Excel

Learn Excel Quickly
Get full list of Excel Macro Codes