My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / List Files in a Folder Using FILES XL Macro (XLM)
List Files in Folder Using FILES XLM
by

Way back, when Excel 4.0 was around, it used a macro language called XLM. VBA was introduced in Excel 5.0.

These XLM macros are still in Excel and you can use them to do certain cool things. One of these macros is FILES.

Now I call these macros, but when you use them you just use them like any function in your worksheet. You don't have to do any kind of programming.

The neat trick with FILES though is how you combine it with a defined name, I first saw this over at Jan-Karel Pieterse's blog. Let's see how it works.

Listing Files in a Folder

I have previously written a couple of articles using VBA to Create a Hyperlinked List of Files in a Folder and to Create a Hyperlinked List of Files in Subfolders.

Using FILES we can create a list of the files in a folder, but these are not hyperlinked so clicking on the file name won't open the file.

To start, let's enter into A1 the full path and search string we want to use to list our files. You can use wildcards here e.g.

file search path and string

Next we define a name, so open Name Manager (CTRL + F3) and create a new name called MyFiles.

In the Refers to box enter =FILES($A$1), then click OK.

FILES defined name

You should now have a new name like so

create new defined name

Close the Name Manager.

Now for the fun part. In B1 enter the formula =INDEX(MyFiles,ROW()), hit Enter and the first file that matches your search parameters will be listed.

Copy this formula down the column and each file in the folder will be listed in turn until you end up with #REF! errors indicating no more files to be listed.

You can edit the search parameters in A1, and the file list will automatically update.

List of files

#REF Errors

To remove these unsightly errors you can wrap the formula in column B in an IFERROR e.g. =IFERROR(INDEX(MyFiles,ROW()),"-----"), so when there are no more files left, you get ----- displayed which is more visually appealing than #REF!. You can put whatever string you like in here of course.

Final Points

The workbook must be saved as a macro enabled workbook, even though you haven't actually written any VBA code.

I haven't figured out a way to work out how many files are in the folder, which would tell me how many rows I need to copy the formula down to. If you don't use enough rows you risk missing some files, so just use lots and lots if you're not sure 🙂

Download Sample Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
List Files in Folder Using FILES XLM

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category:

Reader Interactions

Comments

  1. Harish

    Hi,

    To find out the files in the folder using Excel 4.0 Macro function. We can get the count of the file using

    =COUNTA(MyFiles)

    Regards,

    Harish

    Reply

  2. BerniceS

    Hi Mynda, I had to return to this post because when I wanted to use it for a project I was working on, it somehow stopped working correctly. It would always miss the first file in the list. So I opened a new workbook and redid it and it worked. Still the answer to what was wrong didn’t strike me at first. Then I looked at everything with an objective eye and realized that I have put headers in the columns. Once I removed that everything worked fine. I scoured your post and I don’t recall seeing anything that indicated this must always be on the first row. I am not sure what is going on behind the scenes in the list function but perhaps you can explain. Thanks ever so much!

    Reply

    • Catalin Bombea

      Hi Bernice,
      The ROW() function, f there is no reference indicated, will return the current row number. Obviously, if you start the list in first row, ROW() function will return 1, and INDEX will return the first file.
      You can start the list anywhere, just make sure you type a reference to first row in the ROW argument:
      =INDEX(MyFiles,ROW(A1))
      When you will copy this formula down, it will create the correct list, no matter where you have this formula.

      Reply

  4. Carey Sundberg

    I am seeing this one a bit late. While it is working for me, I noticed that it doesn’t return folder names or hidden file names. I do have File Explorer set to show hidden files & folders, but not sure it would extend to the FILES macro.

    Any ideas?

    Carey

    Reply

  5. BerniceS

    Thank you for this! What a simple solution and extremely timely for me as I am in the midst of porting a couple of very large websites and need to track and rename every single page. Was doing this manually and you have just save me a ton of effort!

    Reply

  6. Dennis Plum

    Philip,
    Regarding your “Final Points” concern over how many rows to define, I’ve created a macro that:
    – Clears out any prior files
    – Lists only as many rows as are needed plus 1 for a “–” end of list indicator.
    NOTE: The formula is inserted by the macro only for rows needed.

    The Macro with associated cells for the Path/Wild Card and File Name results is stored in the file at the link below.

    Also, I modified the formula so that the INDEX works for wherever the Rows are.

    One question I have regarding the use of the “FILES” function is… Because this function is from “a bygone Excel era” can we be sure that it will continue to exist in the future?

    Dennis Plum

    The XLSM file with the macro mentioned above can be downloaded at http://dplum.com/macros/Get-File-List-for-Path-and-WildCard.xlsm

    Reply

    • Philip Treacy

      Thanks for all of that Dennis.

      No we can’t be sure that support for XLM macros will continue into the future and that is a point worth noting. Maybe best not to rely on them for critical applications but you can at least use them where they are useful in other non-critical areas.

      Thanks

      Phil

      Reply

  8. Fabio

    Hi Philip,

    Thank you for the trick!
    Re. your final point “I haven’t figured out a way to work out how many files are in the folder”, I tried to use the following formula: =COUNTA(MyFiles) and it seems to work…
    Am I missing something?

    Reply

  9. Shawn Shafer

    You can count the number of files returned by the named range with sumproduct. This works for me and is dynamic when changing the directory or extension.

    =SUMPRODUCT(–(MyFiles<>””))

    Reply

  10. Gerben

    Even when I use the sample workbook it is not working. You can see that the file in Temp directory is “selected” but the index formula still gives an error.

    Reply

    • Philip Treacy

      Hi Gerben,

      Hard to say what’s happening without seeing what you are. Maybe you can open a Helpdesk ticket and provide screenshots of the error, plus details of what you are doing, what folder you are selecting etc.

      Regards

      Phil

      Reply

  13. Lisa

    This looks like it may be very useful. Thanks for the tip. I was wondering if it is possible if it could be modified for a SharePoint folder? I tried mapping the folder on my PC and also using the url and can’t seem to get it working.

    Reply

    • Philip Treacy

      Hi Lisa,

      I don’t have a Sharepoint server here to test with, but I have mapped drives to other computers on our network, and this works fine with them.

      For example, I mapped Z: to \\MYNDA-W10\Users and then in Excel entered z:\mynda\documents\*.xls into A1. This lists all .xls files in that folder.

      I tried using the UNC path directly into A1 (\\MYNDA-W10\Users\mynda\documents\*.xls) but that didn’t work. But mapped drives seem to work fine.

      Regards

      Phil

      Reply

    • BerniceS

      Hi Lisa, If you haven’t got it working, then it might have something to do with authentication of the mapped drive which works directly from your desktop as the login is transparent but from another product calling to it maybe not? Not sure how you would troubleshoot that.

      Reply

  14. Misca

    Love the idea!

    Also, if you need the hyperlinks you can (quite) easily add them using the HYPERLINK-function:

    =IFERROR(HYPERLINK(LEFT($A$1,FIND(“*”,$A$1)-1)&INDEX(MyFiles,ROW()),INDEX(MyFiles,ROW())),”—–“)

    Reply

  15. Glenn Case

    Phil:

    I’ve tried this with a number of different folders, confirmed that the formulas match yours, but all I get is a string of #N/A errors. I’m using Excel 2016. Any thoughts?

    Reply

  16. Graham

    Why not use the built in functionality of Power Query to list files in a folder? It would also extract file properties for each file found and along with summarising file type counts by referencing the original query…

    Reply

  17. SunnyKow

    Hi Philip

    In your sample workbook the formula starts in B8 instead of B1 and this will result in 7 file names short extracted.

    Sunny

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *