Assume a folder named Test on the desktop. In this Test folder, there is another folder named Survey reports. In the Survey reports folder, there are Word documents, PowerPoint files, Excel files, PDF files and all sort of other files types.
In a new workbook, one may want to list down all Excel files from the Survey reports folder (saved on the desktop) without using VBA. Furthermore, for new Excel files added to the folder, the list should keep updating.
Solution for MS Excel 2010 and higher versions
If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table). In Power Query, there is a feature to append data from multiple workbooks into one running range. The detailed process to do so is described below:
1. Go to Power Query > From File > From Folder
2. Choose the Test folder on the desktop and click on OK
3. Filter the extension column on “Contains” .xls
4. Right click on the Name column heading and choose “Remove Other columns”
5. Click on Apply and Close
This should now list down all MS Excel files lying the Test folder. This is a dynamic list i.e. add or remove Excel files from the folder and just right click on any cell and select Refresh.
Solution for all versions of MS Excel
Try the following procedure to get a list of all Excel files from the folder:
1. In the new workbook, press Ctrl+F3 > New. In the name box, type FL
2. In the Refer to box, enter =FILES(“C:\Users\Ashish\Desktop\Test\Survey Reports\*.xls”)&T(NOW()).
3. Click on OK and Close
In cell A3 of sheet1, enter the following formula and copy down
=IF(ISERROR(INDEX(FL,ROW()-2)),””,INDEX(FL,ROW()-2))
If you add another Excel file to the Survey report folder, just press F9 anywhere on sheet1 and the file will appear in the list.
Change C:\Users\Ashish\Desktop\Test\Survey Reports\ to your actual path from where you want to extract the Excel files.
To generate a list of all tab names from in an Excel file, you may refer to the following post.
Generate a list of all Excel files from a specific folder without using VBA
{ 56 Comments }