J-Walk & Associates, Inc.

New Tip: Listing File Names In A Range

Today, by accident, I discovered another way of getting a list of file names into a range. I posted it in a tip called Getting A List Of Files Names - Another Method.

It's pretty simple, and involves listing the files in your browser, then copying a pasting. Internet Explorer users need not apply. It doesn't work with that browser.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Haffy. Comment posted 27 January, 2009 10:05am

   Not as easy, but do you know Bob Umlas's method from "This isn't Excel it's magic"? Create a Name with the definition =FILES() which uses some old XLM to produce an array of filenames in the current diretory. If the Name is xlmFiles you can then use =INDEX(xlmFiles,ROW()) in row 1 to get the first filename, then copy the formula down.
2. By John Walkenbach. Comment posted 27 January, 2009 12:10pm

   I just looked it up in Bob's book. Page 53. It even works in Excel 2007.
   
   If I ever knew about that technique, I forgot about it.
3. By Neale. Comment posted 28 January, 2009 6:46pm

   Great formula. Interestingly it isn't dynamic. Adding a file to the folder doesn't update the formula. Also saving the file to another folder doesn't give you a different list. So you need to delete and recreate the range name to get the latest list - not a major issue.
4. By John Walkenbach. Comment posted 28 January, 2009 7:11pm

   Not a major issue -- and not really a good way of getting a list of files. I think Umlas presented it more as an interesting XLM curiosity than a really useful feature. And I wouldn't rely on it working in future versions.
5. By Haffy. Comment posted 29 January, 2009 2:12pm

   To be honest I prefer your first solution, JW. Oh the joy of character line computing and none of this GUI nonsense!