Generate a list of all Excel files from a specific folder without using VBA

{ 56 Comments }

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.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • I tried crawling through subdirectories using:

    FILES(“C:\Users\Ashish\Desktop\Test\*\*.xls”)&T(NOW())

    but it doesn’t seem to work that way. Do you know of any methods to crawl directories up to 2 deep? Many thanks!

      • Sorry for the lack of clarity.

        Let’s assume that I have a folder with the names of people who take a survey. Within each named folder, there is a folder for the year they took the survey. Then, in that year folder, you have the excel files for the survey.

        For example,
        ..\casefiles\John\2011\*.xlsx
        ..\casefiles\John\2012\*.xlsx
        ..\casefiles\Janet\2012\*.xlsx

        Now, I need to get a listing of the names of every xlsx file within every subfolder of “casefiles.” The printout in Excel should read “John\2011\October.xls”, “John\2011\February.xls”, etc, (order doesn’t matter so much..that’s easy to manipulate in excel.

        I hope that helps clarify things a bit.

        • Hi,

          The technique cannot crawl through subdirectories which are unknown. The * wildcard will only work for unknown file names, not for unknown folders. Here is my workaround:

          1. On my desktop, I created the following folder structures
          a. C:\Users\Ashish\Desktop\Data\Casefiles\John\2011
          b. C:\Users\Ashish\Desktop\Data\Casefiles\John\2012
          c. C:\Users\Ashish\Desktop\Data\Casefiles\Janet\2011
          2. Under the last sub folders, I saved multiple Excel, Word and PPT files
          3. In a new workbook, I typed John, John and Janet in range B3:B5 & 2011, 2012 and 2011 in range C3:C5
          4. I defined a name – FL, with the following formula in the Refers to box

          =FILES("C:\Users\Ashish\Desktop\Data\Casefiles\" & Sheet1!$B3 & "\" & Sheet1!$C3 & "\*.xlsx")&T(NOW())

          5. In cell A3, I entered the following formula and copied down

          =B3&"\"&C3&"\"&INDEX(FL,1)

          Looks like step 3 above is the impediment but I cannot seem to figure out a non macro based better solution.

          Please share your thoughts.

  • Using this method, is it possible for the returned list of data to not list the file extension along with the file name.

    Example:

    Current Result:
    Sheet1′!A3:A20 = filename1.xls, filename2.xls, filename3.xls, etc…

    Requested Result:
    Sheet1′!A3:A20 = filename1, filename2, filename3, etc…

  • Hi,
    Is there a possibility to add the new excel version extensions, like .xlsx; .xlsm etc? or can I create a new “Define Name” for each extension and use the same formula?

    Thanks,

  • Hi,

    This is working for me except that it always seems to find one less file in the specified folder than is actually there, e.g. I have 43 files in the folder, this formula lists 42 of them. If I remove one folder, it only finds 41, and so on.

    Any thoughts as to the reason?

    Win7, Office 2010.

    Thanks,

  • Yes, that works. Is it having it in A1 that makes the difference? Oddly it seemed to fix the other formula too.

    Not to worry though. Thanks for your help. This is such a brilliant little tool!

  • Great info. I use the formula in cell A1 and down to capture all xls files in a specific folder. However, is there a way I can capture the data (ex all infor in sheet 1, cell A1 of every file in that folder populate in the B colume, lining up with the file naem it came from)?

  • Dear Ashish,
    Need help.

    I wish to generate a list of all ’Worksheet Name’ & ’Their Tab’ Name from a specific folder USING VBA.

    Result Get 3 Columns
    ’Folder Directory Name’ ’Worksheet Name with Extension’ & ’Tabs Name’

    Pls see if you can help me on this.

    Thanks & Best regards,
    Sajjad

    • Hi,

      You may refer to my solution in this workbook.

      Here are the instructions:

      1. In cell F1 of this workbook, enter the path of the folder where the files are e.g. C:\Users\Ashish\Desktop\Data
      2. In the Data folder there can be all sorts of files i.e. PPT, Word, Excel (.xls, .xlsx, .xlsm) etc.
      3. Press Alt+F8 and run the macro.

      Hope this helps.

      • Dear Ashish,
        Thanks for your solution, it’s working.

        Possible to get same data without opening the files (like using ADO connection),
        cause if we have more thn 100+ files in the folder it would have lot of time to collect the data.

        Pls see if we have same solution (for the closed files)

        Thanks once again.

        Regards,
        Sajjad

  • Could you make a downloadfile to illustrate the function?
    I have excel in another language and can not find the translation of the word “FILES” used in the nameboxformula “=FILES(“C:\Users\Ashish\Desktop\Test\Survey Reports\*.xls”)&T(NOW()) “.
    Formulas in an excelfile will be translated automaticly.
    Thanks for this great site.

  • I followed the above instructions and dragged the formula all the way down, but the file names stop after row 258 when I have 600 files in the folder. Any ideas? Thank you.

    • Hi,

      This seems to be a limitation with XLM 4.0 macros. If you are using Excel 2010 or a higher version, then please download the free Power Query add-in (from Microsoft) and follow the steps in my original post.

      Hope this helps.

  • I am having the same issue, it stops at 258 and the rest of cells below contain the formula but are blank. I am trying to transfer over 2,700 excel files to then extract one cell of data for each in another one of your formulas

    • Hi,

      This seems to be a limitation with XLM 4.0 macros. If you are using Excel 2010 or a higher version, then please download the free Power Query add-in (from Microsoft) and follow the steps in my original post.

      Hope this helps.

  • Dear Ashish,
    I need help I want to open an excel file from the formula
    as per your formula my Excel sheet shows
    1 Book2.xlsx
    2 Book3.xlsx
    3 Book4.xlsx
    4 Book5.xlsx
    5 Book6.xlsx

    NOW IF I CLICK ON BOOK2.XLSX THAN THAT PARTICULAR SHEET SHOULD GET OPEN …SOMETHING LIKE HYPERLINK
    IS THERE ANY WAY THAT THIS QUERY CAN BE SOLVED ?

    • Hi,

      Yes, you can. Please read up on the HYPERLINK() function in the Help menu (F1) of MS Excel. I do not have access to my computer right now so i cannot write it for you but try it yourself and if you face a problem, please revert.

  • This is very good, thanks, but can anyone shed light on why numeric filenames of the type 2014-03-20.xlsm are not listed when other files are?

    • Hi,

      In the Refers to box of Name Manager, FL should refer to the following formula

      =FILES(“C:\Users\Ashish\Desktop\Test\Survey Reports\*.xls*”)&T(NOW())

      I have tried and tested it. It works fine.

  • I’m sorry I misled you without even trying. I don’t think the extension matters, it behaves the same with *.*, *.xls* and *.xlsm.

    The directory listing shows template.xlsm and log.xlsm but will not show 2014-03-20.xlsm or other daily files with similar names. Is there any reason why a filename of that sort should be filtered out?

  • Ok, sorted it and it’s nothing to do with the filemask.

    Your method works perfectly but only from cell A3. INDEX(FL,ROW() starts from row 1 and has to have the correct offset hardcoded in, -2 in order to list from A3.

    So when I started listing from row 10 it ignored the first few files, which so happened to be all the yyyy-mm-dd.xlsm files and nothing else.

    So by using
    =IF(ISERROR(INDEX(FL,ROW()-10)),””,INDEX(FL,ROW()-10))
    it works fine from row 10.

    Thanks for your help 🙂

  • It’s brilliant!
    I tested it and it works perfectly fine.
    I am wondering if there is also a way to count number of Excel files in the folder using the formula. Do you know how to to do it?
    Thanks!

  • Hi Ashish

    I am trying to use your formula to extract a specific list of worksheet names rather than all names. is it possible to adapt your formula to do this or do you have a suggestion for another formula?

    =IFERROR(INDEX(MID(Sheets,FIND(“]”,Sheets)+1,255),SMALL(IF(MID(Sheets,FIND(“]”,Sheets)+1,255)MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255),ROW(INDIRECT(“$A$1:$A$”&COUNTA(MID(Sheets,FIND(“]”,Sheets)+1,255))))),ROW(1:1)),1),””)

    to only list worksheet names that begin with “TEST” there are 500 worksheets named Test1 thru 500 and could be more added.

    Regards

    John

    • Hi,

      Try this array formula (Ctrl+Shift+Enter)

      =IFERROR(INDEX(MID(Sheets,FIND(“]”,Sheets)+1,255),SMALL(IF(ISNUMBER(SEARCH(“Test”,MID(Sheets,FIND(“]”,Sheets)+1,255))),ROW(INDIRECT(“$A$1:$A$”&COUNTA(MID(Sheets,FIND(“]”,Sheets)+1,255))))),ROW(1:1)),1),””)

      Hope this helps.

    • Hi,

      I just tried it. It works perfectly. I have the following files saved in the folder – 1Ashish and 2Try. These names appeared in the workbook where I wanted to create the list.

  • hello
    rare to find expert like you ,
    please help if possible : by using your way can we get files in subfolder also ?

    Thanks

  • Is there any method by which the FILES function can be set to only update/calculate when ctrl-alt-f9 is pressed. I have the formula that uses FILES going down 5000 rows (to extract the file names from a folder) and have determined that the FILES function is slowing things down. Maybe, I’ll have to use VBA with an ‘Update’ button.

    • Hi,

      My solution is anyways not a dynamic one – one has to press F9 to update the list. Anyways, in the FILES() formula, remove the &T(NOW()) portion. Now see if the Ctrl+Alt+F9 works.

  • Hi,
    I am using your guide to populate a list of xls documents in my directory.
    Everything works however I cannot get the list in the order it appears in my directory(eg.date order)
    =IF(ISERROR(INDEX(FL,ROW()-2)),””,INDEX(FL,ROW()-2))
    Thanks

  • Valuable article – I loved the insight – Does anyone know if I might get a sample GA MV-1 copy to complete ?

  • Hi there,
    Can the procedure import the “Date Modified”? The way it works now is importing to Excel but in alfabetical order. I need to have the files listed in Excel as the “Date Modified” filter shows them in the Windows Explorer.