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
Enter your email address below to download the sample workbook.
You can download a sample workbook with the name and formula set up.
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
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!
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.
So simple but hidden. Thanks for this tip.
You’re welcome.
Phil
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
Hi Carey,
FILES() won’t list folders or hidden files. You’ll need to use the DIR() function for that and I’ve written code in 2 posts using DIR() that will do what you need:
Create Hyperlinked File List
Hyperlinked File List from Subfolders
Regards
Phil
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!
Hi Bernice,
Glad that it was useful for you.
Regards
Phil
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
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
I prefer to use FileSystemObject to list files.
Hi Graham,
Yes I use FSO in a couple of other posts :
Create a hyperlinked List of Files
Finding File Meta Data
Cheers
Phil
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?
Thanks Fabio
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<>””))
Thanks Shawn
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.
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
COUNTA(MyFiles) – to count number of files.
Thanks Niefer
In order to get the count of files in a folder use “=ArrayLength(MyFiles)”
Thanks Bob
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.
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
Thanks Phil! I will try that.
No worries.
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.
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())),”—–“)
Thanks Misca.
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?
Hi Glenn,
We have to see the file, can you upload a sample on our forum?
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…
Hi Graham,
Power Query is another great way, but not everyone has access to Power Query yet.
Mynda
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
Doh. Thanks Sunny. I’ve amended the file.
Thanks. It is working perfectly now.