Note that I entered the folder location in a cell and then used that cell in the GetFileNames formula. Copy and paste the formula in the cells below to get a list of all the files.In the cell where you want the list, enter the following formula (I am entering it in cell A3): =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),""). In any cell, enter the folder address of the folder from which you want to list the file names.Double click on the Module object and copy and paste the above code into the module code window.īelow are the steps to use this function in a worksheet:.If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key). In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module.The above code will create a function GetFileNames that can be used in the worksheets (just like regular functions).įollow the steps below to copy this code in the VB Editor. Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFSO = CreateObject("Scripting.FileSystemObject") Function GetFileNames(ByVal FolderPath As String) As Variant Now let me first give you the VBA code that will create a function to get the list of all the file names from a folder in Excel. You can also create an add-in and share this function with others. The benefit of using a User Defined Function (UDF) is that you can save the function in a personal macro workbook and reuse it easily without repeating the steps again and again. It’s, however, a lot better than doing this manually.īut if you’re comfortable with using VBA (or if you’re good at following exact steps that I am going to list below), you can create a custom function (UDF) that can easily get you the names of all the files. Now, I must say that the above method is a bit complex (with a number of steps). Using VBA Get a List of All the File Names from a Folder Watch Video – Get List of File Names from a Folder in Excel So ROW()-2 would be 1 for the first instance, 2 for the second instance when the row number is 4, and so on and so forth. Note that I have used ROW()-2, as we started from the third row onwards. In the INDEX formula, we have given the file names as the array and we return the 1st, 2nd, 3rd file names and so on using the ROW function. Similarly, for word document files, use *doc*įILES formula retrieves the names of all the files of the specified extension in the specified folder. So the folder address that you need to use would be C:\Users\Sumit\Desktop\Test Folder\*xls* For example, if you want only excel files, you can use *xls* instead of * If you want to get all the files with a particular extension, just change the asterisk with that file extension. Want to Extract Files with a Specific Extension? Drag this down and it will give you a list of all the file names in the folder.Go to cell A3 (or any cell where you want the list of names to start) and enter the following formula: = IFERROR(INDEX(FileNameList, ROW()-2),"")
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |