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)

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.