Getting the list of File Names in Microsoft Excel 2013

How about a trick that gets the names of the files within a folder and displays them in a worksheet in Excel 2013 ?

Here we go . We can use the Excel XLM macro function to achieve it.

How to get the list of file names in Microsoft Excel 2013 ?

1. Launch Microsoft Excel 2013 and create a new Excel document.
2. In Microsoft Excel , select formula –> define Name . This will display the New Name dialog box. Provide the name as “GinktageFileList” . Enter the below formula in the refers to field.

=FILES(Sheet1!$A$1)

image

3. The Files function in excel takes the parameter which is the directory path with the file extension and returns the array of file names . Enter the directory path and the file extension in the cell A1 . For example , the value in cell A1 can be E:\*.xl*

4. Select the cell where you want to display the file name , and add the below value

=INDEX(GinktageFileList, 1)

This will display the first available file in the selected path . If you want to get the 2nd file , change the second parameter to 2.

5. To get the complete list , use the below formula in a cell .

=INDEX(GinktageFileList,ROW()-1)

Once the formula is added , select the cell and select the “Copy cells” options . Alternatively , copy the cell value (formula) and paste it in the below cell to retrieve the next file name.

The ROW function is used to, generates a series of consecutive integers like 1, 2, 3 etc.

Don’t forget to save the file as macro-enabled file (xlsm) if you wanted the above code to work fine to retrieve the file names.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.