Getting A List Of Files Names - Another Method
In a previous tip, I described method of putting a list of file names from a directory into an Excel range. Today I discovered another method that's much simpler. It uses the File protocol in a Web browser.
- Note: This method does not work with Internet Explorer. I tested it with Firefox and Chrome, and it works fine with those browsers.
It's pretty simple:
- Enter the directory path in your browser's address bar. For example,
The browser will convert this path into a URI (Uniform Resource Identifier). For example, g:\music is converted to file:///g:/music/. In addition, the files in that directory will be displayed as hyperlinks, and you can traverse the directory by clicking on directory names. Here's how the file listing appears in Firefox:
- Press Ctrl+A to select all of the text, and then Ctrl+C to copy it.
- Activate Excel, select a cell, and press Ctrl+V to paste.
The filenames will be pasted as hyperlinks with wrapped text. You might prefer to use Paste Special (with the Text option) rather than Ctrl+V. You'll get a three-column range like this (copied from Firefox):
Google's Chrome browser is actually better for this task because it doesn't precede the file names with "File:" and it puts the date and time in a single cell.
This technique works with a single directory. If you need to get a list of files in multiple directories, you must repeat the procedure for each directory.
For more flexibility, use the Filename Lister utility in my PUP add-in.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks