![]() ![]() If InStr(1, fileObj.Name, FileExtension) 0 Then Note: If the Project window is not open, activate it by pressing Ctrl + R or choosing Project Explorer on the View menu.įunction EXTRACTFILENAMESBYEXT(ByVal FolderPath As String, FileExtension As String) As Variant We can create a User-Defined Function that we can use to return only those file names with a particular extension. Sometimes we may want a list of names of only those files with a specific extension, for example. Using User-Defined Function to Get List of File Names With a Specific Extension Notice that this technique did not return the names of the files in the “Excel Tutorials 2” subfolder. Next, ROW()-3 equals 5-3 when the row number is 5, returns the second file name, and so on.įinally, the IFERROR function enclosing the formula suppresses the #REF! errors and returns empty strings after the formula returns the last file name. So ROW()-3, equivalent to 4-3 when the row number is 4, returns the first file name. We used ROW()-3 in the formula because we started the list in the fourth row. The array of file names is then fed into the INDEX function, and the ROW function returns the first file name, second file name, third file name, and so on. In INDEX(EXTRACTFILENAMES($A$1),ROW()-3) part of the formula, the EXTRACTFILENAMES function returns an array of all the file names of the files in the main folder. If you want to start the list in row 1, you can modify the formula as below: =IFERROR(INDEX(EXTRACTFILENAMES("C:\Excel Tutorials"),ROW()),"") Explanation of the Formula Note: In case you do not want to enter the path of the folder in a cell, you can hard-code the path into the formula as shown below: =IFERROR(INDEX(EXTRACTFILENAMES("C:\Excel Tutorials"),ROW()-3),"") In this case, we enter the full path in cell A1:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |