What Function Is Used to Copy the Names of the Files in a Directory to an Array?

Become the List of File Names from a Folder in Excel (with and without VBA)

• -- By Sumit Bansal

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

The piece of work was simple.

At that place 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 copy its name.
2. Paste that proper noun in a cell in Excel and hit Enter.
3. Motility to the adjacent file and repeat stride ane & 2.

Sounds unproblematic right?

It was – Elementary and a huge waste of time.

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

In this tutorial, I will show yous dissimilar ways to make this entire process super fast and super easy (with and without VBA).

• Using FILES Part to Become a List of File Names from a Folder
• Using VBA Get a Listing of All the File Names from a Folder
• Using VBA Go 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 will only be able to get the names of the files inside the chief binder. You volition not get the names of the files in the sub-folders within the main binder. Here is a way to get names of files from folders and sub-folders using Power Query

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

Heard of FILES role before?

Don't worry if you haven't.

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

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

Now, suppose you lot accept a folder with the name – 'Exam Folder' on the desktop, and you want to go a list of file names for all the files in this folder.

Here are the steps that volition give yous the file names from this folder:

1. In prison cell A1, enter the folder complete accost followed by an asterisk sign (*)
   • For instance, if your folder in the C drive, then the address would await like
     C:\Users\Sumit\Desktop\Test Folder\*
   • If you are non sure how to get the folder accost, utilize the following method:
     • • In the folder from which yous want to become the file names, either create a new Excel Workbook or open an existing workbook in the folder and utilize the below formula in any jail cell. This formula will give you lot the folder accost and adds an asterisks sign (*) at the finish. Now you tin copy-paste (paste as value) this accost in any cell (A1 in this example) in the workbook in which you want the file names.

                                                                   =REPLACE(CELL("filename"),FIND("[",Jail cell("filename")),LEN(Jail cell("filename")),"*")                                                      

         [If you have created a new workbook in the binder to apply the above formula and get the folder address, you may want to delete it so that it doesn't characteristic in the list of files in that folder]

2. Get to the 'Formulas' tab and click on the 'Define Proper name' option.
3. In the New Name dialogue box, use the following details
   • Name: FileNameList (feel free to cull whatever name you lot like)
   • Telescopic: Workbook
   • Refers to:=FILES(Sheet1!$A$1)
4. Now to get the list of files, we will apply the named range within an INDEX function. Go to jail cell A3 (or any prison cell where you want the list of names to offset) and enter the following formula:

                   =IFERROR(INDEX(FileNameList,ROW()-2),"")              

5. Drag this down and it will give you lot a list of all the file names in the folder

Want to Extract Files with a Specific Extension??

If you want to become all the files with a particular extension, just alter the asterisk with that file extension. For case, if you want just excel files, y'all tin utilise *xls* instead of *

So the folder address that you demand to use would exist C:\Users\Sumit\Desktop\Test Folder\*xls*

Similarly, for word document files, employ *doctor*

How does this work?

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

In the Alphabetize formula, we take given the file names equally the array and we return the 1st, 2nd, 3rd file names and so on using the ROW office.

Note that I have used ROW()-ii, as we started from the third row onwards. So ROW()-2 would be ane for the kickoff instance, 2 for the second instance when the row number is 4, and then on and so along.

Spotter Video – Become List of File Names from a Folder in Excel

Using VBA Get 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, withal, a lot amend than doing this manually.

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

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

Now permit me offset give you the VBA code that volition create a function to get the list of all the file names from a folder in Excel.

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

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

Where to put this lawmaking?

Follow the steps beneath to copy this code in the VB Editor.

How to Use this Function?

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

• In any cell, enter the folder address of the binder from which you want to list the file names.
• In the prison cell where you lot want the list, enter the following formula (I am entering it in jail cell A3):

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

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

Notation that I entered the folder location in a cell and so used that cell in the GetFileNames formula. You can also hard code the folder address in the formula as shown below:

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

In the above formula, nosotros accept used ROW()-2 and we started from the tertiary row onwards. This made sure that every bit I copy the formula in the cells below, it volition get incremented by 1. In case you're inbound the formula in the first row of a column, you can simply use ROW().

How does this formula piece of work?

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

The Alphabetize function is used to list one file name per cell, starting from the first i.

IFERROR function is used to return blank instead of the #REF! fault which is shown when a formula is copied in a prison cell but there are no more than 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 get a list of all the file names from a binder in Excel.

Just what if y'all want to go the names of only the video files, or only the Excel files, or only the file names that incorporate a specific keyword.

In that example, you can use a slightly different function.

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

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

The above code volition create a function 'GetFileNamesbyExt' that can be used in the worksheets (just like regular functions).

This function takes two arguments – the binder location and the extension keyword. Information technology returns an array of file names that match the given extension. If no extension or keyword is specified, information technology volition return all the file names in the specified binder.

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

Where to put this code?

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

• Go to the Developer tab.
• Click on the Visual Bones button. This volition 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 yous don't come across the Project Explorer, utilize the keyboard shortcut Control + R (hold the control fundamental and press the 'R' key).
• Double click on the Module object and copy and paste the to a higher place code into the module code window.

How to Use this Office?

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

• In whatsoever cell, enter the folder accost of the folder from which you want to list the file names. I accept entered this in cell A1.
• In a jail cell, enter the extension (or the keyword), for which yous want all the file names. I take entered this in cell B1.
• In the jail cell where you want the list, enter the post-obit formula (I am entering information technology in prison cell A3):

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

• Copy and paste the formula in the cells below to become a list of all the files.

How almost yous? Any Excel tricks that you use to make life easy. I would love to learn from you lot. Share it in the comment section!

Yous May Also Like the Following Excel Tutorials:

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

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

wallparrived1974.blogspot.com

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