Save the procedure and the workbook as a Macro-Enabled Workbook (*.xlsm).Set folderObj = fsoObj.GetFolder(FolderPath) Set fsoObj = CreateObject("Scripting.FileSystemObject") Copy and paste the following function procedure into the module’s code window.įunction EXTRACTFILENAMES(ByVal FolderPath As String) As Variant.Double-click the new module to open its code window on the right.In the Visual Basic Editor, select any object of the workbook you are working on, click Insert on the menu bar, and choose Module.Press Alt + F11 to launch the Visual Basic Editor.We use the below steps to create the User Defined Function: The advantage of this method over Method #1 is that the function can be saved in a personal macro workbook and reused without repeating the steps. We can create a User Defined Function using Excel VBA to return the names of files in a folder. Also read: Extract Last Name in Excel Using a User-Defined Function (Created using VBA) Notice that this technique did not return the files names of the files in the “Excel Tutorials 2” subfolder. Lastly, the IFERROR function wrapping the formula suppresses the #REF! Error and returns an empty string after the formula returns the last file name. Next, ROW()-3 equals 2 (i.e., 5-3) when the row number is 5, returns the second file name, and so on. So ROW()-3, equivalent to 4-3 when the row number is 4, returns the first file name. Notice that we used ROW()-3 because we started the list in the fourth row. In the =IFERROR(INDEX(List_File_Names,ROW()-3),””) formula, the names returned by the FILES function are fed to the IINDEX function as an array, and then the ROW function returns the first file name, second file name, third file name, and so on. The FILES formula, =FILES(Sheet1!$A$1), extracts the filenames of the files in the main folder. But when you use *xlsm, it forces the FILES function to only give us the list of those file names that end with xlsm Explanation of the FILES function technique When you use an asterisk symbol (*) at the end of the folder path address, it tells the FILES function to fetch all the files irrespective of the file extension.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |