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.
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.
You should now have a new name like so
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.
#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
You can download a sample workbook with the name and formula set up.
){kind=link}
Dennis Plum says
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
Philip Treacy says
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
Graham Gibbons says
I prefer to use FileSystemObject to list files.
Philip Treacy says
Hi Graham,
Yes I use FSO in a couple of other posts :
Create a hyperlinked List of Files
Finding File Meta Data
Cheers
Phil
Fabio says
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?
Philip Treacy says
Thanks Fabio
Shawn Shafer says
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<>””))
Philip Treacy says
Thanks Shawn
Gerben says
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.
Philip Treacy says
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
Niefer says
COUNTA(MyFiles) – to count number of files.
Philip Treacy says
Thanks Niefer
Bob Cox says
In order to get the count of files in a folder use “=ArrayLength(MyFiles)”
Philip Treacy says
Thanks Bob
Lisa says
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.
Philip Treacy says
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
Lisa says
Thanks Phil! I will try that.
Philip Treacy says
No worries.
Misca says
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())),”—–“)
Philip Treacy says
Thanks Misca.
Glenn Case says
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?
Catalin Bombea says
Hi Glenn,
We have to see the file, can you upload a sample on our forum?
Graham says
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…
Mynda Treacy says
Hi Graham,
Power Query is another great way, but not everyone has access to Power Query yet.
Mynda
SunnyKow says
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
Philip Treacy says
Doh. Thanks Sunny. I’ve amended the file.
SunnyKow says
Thanks. It is working perfectly now.