Getting A List Of File Names
Many users are surprised to discover that Windows does not provide a direct way to get a list of file names contained in a directory. This tip describes how to create such a list in a text file, which can then be imported into Excel.
To generate a list of file names, you'll need to use a DOS command typed in a DOS command window. To open a DOS command window:
- Click the Windows Start button
- Click Run
- Type "cmd" (no quotes) and press Enter. if "cmd" doesn't work, use "command".
You'll get a window like the one shown below.
Next, you need to type a DOS command to generate the file list. For example, if you would like to generate a list of all files in the root directory of drive D, type the following at the command prompt and press Enter:
To list the files in a particular directory, add the directory name after the drive:
dir d:\my files\
The file names will be listed in the window. Usually, you'll want these files to be sent to a file. To redirect the output to a file, use the > character and specify a file name. For example, to send the file names to a text file named filelist.txt in the root directory of drive C, use this command:
dir d:\ >c:\filelist.txt
If you would like the file list to include the files in all subdirectories of drive D, use the /s switch:
dir d:\ /s >c:\filelist.txt
The directory listing will contain lots of additional information. To get the file names only (bare format), use the /b switch:
dir d:\ /s /b >c:\filelist.txt
To find out about other options available for the DIR command (such as including file dates and times), type this command:
After the text file is generated, you can import it into Excel by using the File - Open command.
NOTE: If you need do this on a regular basis, you may be interested in this article from Microsoft, which describes how to create a batch file that lists file names in Notepad.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
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