A Folder or File and Try Again

Get the List of File Names from a Binder in Excel (with and without VBA)

• -- Past Sumit Bansal

On my commencement solar day in my task in a small consulting firm, I was staffed on a short project for three days.

The work was uncomplicated.

There were many folders on the network drive and each folder had hundreds of files in it.

I had to follow these three steps:

1. Select the file and re-create its name.
2. Paste that name in a prison cell in Excel and striking Enter.
3. Move to the adjacent file and repeat footstep i & 2.

Sounds simple right?

It was – Simple and a huge waste of time.

What took me three days could have been done in a few minutes if I knew the right techniques.

In this tutorial, I will show you different ways to make this unabridged process super fast and super like shooting fish in a barrel (with and without VBA).

• Using FILES Function to Get a List of File Names from a Folder
• Using VBA Go a Listing of All the File Names from a Folder
• Using VBA Get a List of All the File Names with a Specific Extension

Limitations of the methods shown in this tutorial: With the techniques shown below, you lot volition only be able to get the names of the files within the main binder. You will not get the names of the files in the sub-folders within the main folder. Hither is a mode to become names of files from folders and sub-folders using Ability Query

Using FILES Role to Get a List of File Names from a Binder

Heard of FILES role before?

Don't worry if you haven't.

It is from the childhood days of Excel spreadsheets (a version 4 formula).

While this formula does not work in the worksheet cells, information technology still works in named ranges. Nosotros will use this fact to go the list of file names from a specified folder.

At present, suppose yous have a binder with the name – 'Examination Folder' on the desktop, and you want to become a listing of file names for all the files in this folder.

Here are the steps that will requite you the file names from this binder:

1. In cell A1, enter the binder complete address followed by an asterisk sign (*)
   • For case, if your binder in the C drive, then the address would await like
     C:\Users\Sumit\Desktop\Exam Folder\*
   • If you are not sure how to get the folder address, use the post-obit method:
     • • In the folder from which yous want to get the file names, either create a new Excel Workbook or open an existing workbook in the folder and use the beneath formula in any prison cell. This formula will requite you the folder address and adds an asterisks sign (*) at the end. Now you lot tin re-create-paste (paste as value) this address in any cell (A1 in this example) in the workbook in which you want the file names.

                                                                   =Supersede(Prison cell("filename"),Detect("[",CELL("filename")),LEN(CELL("filename")),"*")                                                      

         [If you take created a new workbook in the binder to use the above formula and get the folder accost, you may desire to delete it and so that it doesn't feature in the list of files in that folder]

2. Go to the 'Formulas' tab and click on the 'Define Name' option.
3. In the New Name dialogue box, use the post-obit details
   • Name: FileNameList (feel costless to choose whatsoever name you like)
   • Scope: Workbook
   • Refers to:=FILES(Sheet1!$A$1)
4. Now to get the listing of files, we will use the named range inside an INDEX function. Go to cell A3 (or whatever prison cell where you want the list of names to get-go) and enter the post-obit formula:

                   =IFERROR(Index(FileNameList,ROW()-two),"")              

5. Drag this downwards and information technology will give you a list of all the file names in the folder

Want to Extract Files with a Specific Extension??

If you desire to become all the files with a item extension, just change the asterisk with that file extension. For case, if yous desire merely excel files, you lot can use *xls* instead of *

So the folder accost that you need to utilise would be C:\Users\Sumit\Desktop\Test Folder\*xls*

Similarly, for word document files, use *md*

How does this work?

FILES formula retrieves the names of all the files of the specified extension in the specified binder.

In the Alphabetize formula, we accept given the file names as the array and we return the 1st, 2d, 3rd file names and so on using the ROW part.

Note that I take used ROW()-2, equally we started from the 3rd row onwards. Then ROW()-2 would be 1 for the first case, ii for the second case when the row number is 4, and so on and so forth.

Watch Video – Get List of File Names from a Folder in Excel

Using VBA Become a List of All the File Names from a Folder

Now, I must say that the above method is a bit complex (with a number of steps).

Information technology's, even so, a lot better than doing this manually.

But if you're comfortable with using VBA (or if you're proficient at following exact steps that I am going to list below), y'all tin create a custom function (UDF) that can hands get yous the names of all the files.

The benefit of using a User Defined Function (UDF) is that you tin can salvage the function in a personal macro workbook and reuse it easily without repeating the steps again and over again. You can besides create an add-in and share this function with others.

At present let me first give you the VBA lawmaking that will create a function to get the listing of all the file names from a folder in Excel.

Function GetFileNames(ByVal FolderPath As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile Equally Object Dim MyFSO As Object Dim MyFolder Equally Object Dim MyFiles Equally Object Ready MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Outcome(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles Issue(i) = MyFile.Name i = i + one Next MyFile GetFileNames = Result End Function

The to a higher place lawmaking will create a function GetFileNames that can be used in the worksheets (just similar regular functions).

Where to put this code?

Follow the steps below to re-create this code in the VB Editor.

How to Utilize this Role?

Beneath are the steps to use this function in a worksheet:

• In whatsoever cell, enter the folder accost of the binder from which you want to list the file names.
• In the cell where you want the listing, enter the following formula (I am entering it in cell A3):

                                    =IFERROR(Index(GetFileNames($A$1),ROW()-2),"")                              

• Re-create and paste the formula in the cells below to get a list of all the files.

Note that I entered the folder location in a cell and and then used that cell in the GetFileNames formula. You tin also hard lawmaking the folder address in the formula as shown below:

                          =IFERROR(INDEX(GetFileNames("C:\Users\Sumit\Desktop\Exam Folder"),ROW()-2),"")                      

In the above formula, we have used ROW()-2 and we started from the third row onwards. This made sure that as I re-create the formula in the cells below, it will become incremented past ane. In case you lot're entering the formula in the first row of a cavalcade, y'all tin can merely use ROW().

How does this formula work?

The GetFileNames formula returns an assortment that holds the names of all the files in the folder.

The Alphabetize role is used to listing one file name per cell, starting from the first one.

IFERROR function is used to return blank instead of the #REF! error which is shown when a formula is copied in a cell but at that place are no more file names to list.

Using VBA Get a List of All the File Names with a Specific Extension

The above formula works great when you want to become a list of all the file names from a folder in Excel.

But what if you want to get the names of only the video files, or simply the Excel files, or only the file names that contain a specific keyword.

In that case, you can use a slightly different office.

Below is the code that will allow you get all the file names with a specific keyword in information technology (or of a specific extension).

Function GetFileNamesbyExt(ByVal FolderPath As Cord, FileExt As String) As Variant Dim Result Equally Variant Dim i As Integer Dim MyFile As Object Dim MyFSO Equally Object Dim MyFolder As Object Dim MyFiles Every bit Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Ready MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Upshot(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + one Finish If Next MyFile ReDim Preserve Effect(one To i - i) GetFileNamesbyExt = Result End Function

The in a higher place code will create a function 'GetFileNamesbyExt' that can be used in the worksheets (but like regular functions).

This function takes two arguments – the folder location and the extension keyword. It returns an array of file names that match the given extension. If no extension or keyword is specified, it volition render all the file names in the specified folder.

Syntax: =GetFileNamesbyExt("Folder Location","Extension")

Where to put this code?

Follow the steps below to copy this lawmaking in the VB Editor.

• Get to the Developer tab.
• Click on the Visual Bones button. This will open the VB Editor.
• In the VB Editor, right-click on any of the objects of the workbook y'all're working in, go to Insert and click on Module. If you don't see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the 'R' fundamental).
• Double click on the Module object and re-create and paste the to a higher place code into the module code window.

How to Use this Part?

Below are the steps to use this part in a worksheet:

• In any prison cell, enter the binder address of the folder from which yous want to listing the file names. I have entered this in cell A1.
• In a cell, enter the extension (or the keyword), for which you lot want all the file names. I have entered this in prison cell B1.
• In the prison cell where you desire the list, enter the following formula (I am inbound it in jail cell A3):

                  =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")              

• Re-create and paste the formula in the cells below to get a list of all the files.

How about you? Whatsoever Excel tricks that you utilize to brand life like shooting fish in a barrel. I would love to larn from you. Share information technology in the annotate department!

You May Also Like the Following Excel Tutorials:

• Filter cells with assuming font format.
• How to Combine Multiple Excel Files into 1 Excel Workbook.
• Creating a Drop Down Filter to Excerpt Data Based on Selection.
• Using VBA FileSystemObject (FSO) in Excel.

Get 51 Excel Tips Ebook to skyrocket your productivity and get piece of work done faster

greenandso1943.blogspot.com

Source: https://trumpexcel.com/list-of-file-names-from-a-folder-in-excel/

Share this post