Ever had to get a list of filenames into Excel? You can’t just drag the files into Excel as this will import the complete files. I had to do this recently, and rather than key in the file names one by one, I asked on a forum and got a number of good replies. Here’s the best route I’ve found from that.
The first thing you need to do is create a text file in the file directory listing those file names. Don’t worry, it’s not as hard as it sounds. To make this text file:
1. Open a command prompt: Click Start, then Run, and type cmd
2. Navigate to the directory with your list of file names using the command cd, e.g. type cd c:\photos\2008 and press Enter.
3. Create the text file: Still at the command prompt, type dir /b > tempfile.txt then press Enter
This will create a text file called tempfile.txt with a list of all the files in that directory. Now we have to import the list from that file into Excel:
4. Close the command box by typing exit and click Enter.
5. Open Explore (right click on Start and click on Explore) and navigate to the directory with your list of filenames. The file tempfile.txt should now be there as well.
6. Right click on tempfile.txt and Select Open with then navigate down the list to Microsoft Excel and click on it.
This will import the file listing into a new Excel workbook, but you’ll probably want to get them into another workbook:
7. Select the file listing in Excel by left clicking and dragging to highlight the list (or on the keyboard, hold down the Shift key and click the Down arrow key) and copy the highlighted block with Ctrl-c
8. Open the workbook you want and paste in the listing where you want it using Ctrl-v
You can clean up the listing now:
9. Among the file names you will have imported the file name tempfile.txt. Delete this row in Excel: Highlight that row then click on Edit and then Delete.
10. You’ll also have file extensions which you may not want (e.g. doc): Click Ctrl-H and just search for .doc and replace with blank throughout the spreadsheet.
And that’s it!
Update (10 September 2009): Thanks to a post at How-to Geek, I found a free utility called Dir Print to create the text file of the directory listing. This saves having to go to the command prompt. You can also filter the directory listing to just a particular file type by modifying the box in the top right corner of Dir Print, eg for only Word files, enter *.doc.