Software

Office challenge: What's a quick way to get a list of file names into an Excel spreadsheet?

Learn the answer to last week's Word challenge and test your Excel skills in this week's new challenge.

A user needs to list file names in an Excel sheet. The information's right there in front of her, but how can she get it from her screen into an Excel sheet? You could spend a while writing a VBA procedure, but let's skip that route. There's a quick an easy way to get file names into an Excel sheet--what is it? You aren't restricted to applications—you may use whatever tools you like.

Last week we asked…

What's that red x and where are my graphics? Gbentley suggested checking the Show Picture Placeholders option. This suggestion is good, but not quite right. Placeholders appear empty; they don't display a red x. For future knowledge, it's good to know though, so thanks Gbentley!

The red x indicates a broken link to a linked image file. This happens when you move a linked file or you move the Word document and forget to also move the linked image file. In addition, this can happen if you rename or delete the linked file. Word 2007 goes a long way toward clearing up the mystery by actually telling you what's wrong—you won't find this error message in earlier versions.

The easiest solution is to avoid the problem altogether by embedding instead of linking to the image file. That's not always practical though. Perhaps the next easiest solution is to simply relink the image file—Insert | Picture, and so on. It's quick and easy.

You can repair the link, but the process is a bit lengthy and easy to forget unless this problem occurs so often that you memorize it (and if that's the case, you need to rethink your strategy). To repair a broken link to an image file, do the following:

  1. In Word 2010, click the File tab and choose Info in the left pane. In Word 2007, click the Office tab and click Prepare in the left pane.
  2. In the far-right corner, click Edit Links to Files.
  3. Select the link and click Change Source.
  4. Use the dialog box to locate the file.
  5. Double-click the file.
  6. Click OK.

In Word 2003, do the following:

  1. Select the broken graphic.
  2. Press [Alt]+[F9] to display the field code.
  3. Correct the path.

Frankly, it's simpler to just relink the graphic, unless doing so plays havoc with your formatting.

Stay on top of the latest Microsoft Windows tips and tricks with TechRepublic's Windows Desktop newsletter, delivered every Monday and Thursday. Automatically sign up today!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

65 comments
kironkasbekar
kironkasbekar

There is a very simple way of doing this in Organyze 2.1 (see www.organyze.com). Select one or more folders or sub-folders (they can even be folders from different drives) select all or only some file formats, and then click on "Get list" to get a single list of files from across the selected folders and sub-folders. Just click on Export, and the list will get to an Excel or CSV format, and will include details such as file name, path, created date, last modified date, file type, and size. It takes barely a minute to do this! You can also do this for folders, in which case you get these details plus number of sub-folders, and number of files inside each folder.

RU7
RU7

Using the .csv extension doesn't affect the dir.csv file, it is identical to the dir.txt file, except that when Opening them, the .csv will typically open it in Excel and the .txt will use Notepad. Size is an issue. Of course the bare switch (/b) eliminates the size from the directory. But, if you want size, you don't use the /b switch. Then the size is represented with commas so any size over 999 will send the filename to the next column. Another column offset for sizes over 999,999. The /a-d switch might also be desirable so the list doesn't have an additional row for each sub-directory. The real solution is to import the non-/b .txt file into Excel using fixed cell width. Then you have 5 wonderfully sortable columns: Date, Time, AM/PM, Size, Filename. The first 5 rows and the last 2 rows may be useful. If they are not, they can easily be deleted.

ambraun
ambraun

Try Directory Report http://www.file-utilities.com It can scan your directories and directly send the output to Excel. No need to first save it to a txt or csv file. Plus it can show MP3, EXE, MSI, AVI, WAV and MS-Office properties

hundgenn
hundgenn

Select the files to copy. Right click and Send them to mail client. Mail client will open with files attached with the file names listed in the body of the mail. Copy the file names from the body and paste them in Excel.

RU7
RU7

you have to do it frequently. Get a command prompt in the top directory yo want to list. Enter "dir /b /s /a-d > temp.txt". In Excel 2007 | Data tab Click "From Text" then navigate to and select temp.txt. Select the "Delimited" radio button. Click Next. Under Delimiters click so that only "Other" is checked and enter "\" in its field. Click Next then click Finish. Click OK to start at A1. Go to Sheet 2, cell A1. Enter "=OFFSET(Sheet1!$A$1,ROW()-1,COUNTA(Sheet1!1:1)-1,1,1)". A1 should now have the file name from the first row on Sheet 1. Find out how many rows on Sheet 1 are used. On Sheet 2 go to cell A1 and copy it. In the name box enter "A" followed by the number of rows used on Sheet 1 and press Enter. Press CTRL+Shift+Home. Paste. Click the column A header to select column A. Copy. Click the column B header to select column B. Paste Special | Values (or ALT+E, S, V Enter). Now you can delete Sheet 1 and column A on Sheet 2. This leaves you with a column of file names for the directory and all its sub-directories. Once I had it documented, I did a list of 11,000 in less than a minute.

homesjc
homesjc

DOS forever Goto to target directory invoke CMD, and use DIR *.* > anyfilename eg dir.dat. Use any appropriate filters. Open file in notepad, and cut and paste into where ever. One then needs to select which columns from the text in column on one wants for data. Quick and dirty. Can also get the lot, and then you need to sort the data out for various sub directories. Works with any dos command or prog running under Dos where one can use the redirect command ?>? to capture the screen output. Ofcourse one can write simple VBA macros to sort out the data to your desire. JohnH Perth

Jazz_Euph
Jazz_Euph

dir /b /a-d > list.xls /b is a bare listing without headers /a-d gets rid of directories

jcurrentrbh
jcurrentrbh

super simple solution from cmd: cd dir /b >> file_list.csv and now you have a .csv with all of your file names all in a column :) EDIT: Oops, looks like someone else already posted this.

nzimmerman67
nzimmerman67

Why not cmd dir /b > c:\files.txt Import into excel

bperrill
bperrill

DOS dir > to txt file? Then open in Excel

ps.techrep
ps.techrep

Given: FileNames are displayed on the screen in an unspecified Windows application, presumably a long list that would be time-consuming to rekeyboard. Entire operation must be performed in Windows without recourse to macros or applications that aren't in a default Windows installation. 1. Highlight the section of wanted material on the screen and use copy, (usually Ctrl+C), open notepad and paste it. Turn off wordwrap. 2. If necessary, insert returns to get each FileNamename on a SEPARATE line. 3. Save the file as text. 4. Open the file in Excel as a text/prn/csv file. 4. Use Excel's Data/Text to columns function with or as the delimiter. 5. Eliminate all unwanted columns. (optional) 6. If the names are to be entered into an existing Excel sheet, then copy the block of data and insert or paste it into the destination sheet.

harrylal
harrylal

I have found FreeCommander to be an excellent Windows Explorer replacement. It has a wonderful set of abilities (including the ability to save lists to a file) that far exceed what the MS product can do. To add to all that it doesn't need to be in the registry to work; put it on a flash drive and take it with you.

tadair
tadair

Open a DOS window, do a "DIR" and direct it to a text file (DIR > FILES.TXT), and import the text file into Excel.

mcmarr
mcmarr

Install "Send To Toys" Highlight all the files in the directory Right click the selected files, and click "Send to clipboard as name" Paste them into excle

ljbartel
ljbartel

I use the following VBA to not only collect file names but also return up to 40 other file properties. The properties are as follows: Path, Name, Size, Type, Date Modified, Date Created, Date Accessed, Attributes, Status, Owner, Author, Title, Subject, Category, Pages, Comments, Copyright, Artist, Album Title, Year, Track Number, Genre, Duration, Bit Rate, Protected, Camera Model, Date Picture Taken, Dimensions, Width, Height, Episode Name, Program Description, Date, Audio sample size, Audio sample rate, Channels, Company, Description, File Version, Product Name, Product Version & Keywords. Not every file will have all of the properties. I have an Excel template with these properties in the header row. I placed a button on my worksheet with a call to "GetFileProperties". Here is the module's VBA (this may have some word wrapping issues as posted here. sorry indent is lost too): ' This API declaration is used to return the ' UNC path from a drive letter (no trailing backslash). Private Declare Function WNetGetConnection Lib "mpr.dll" _ Alias "WNetGetConnectionA" _ (ByVal lpszLocalName As String, _ ByVal lpszRemoteName As String, _ cbRemoteName As Long) As Long Const ERROR_BAD_DEVICE = 1200& Const ERROR_CONNECTION_UNAVAIL = 1201& Const ERROR_EXTENDED_ERROR = 1208& Const ERROR_MORE_DATA = 234 Const ERROR_NOT_SUPPORTED = 50& Const ERROR_NO_NET_OR_BAD_PATH = 1203& Const ERROR_NO_NETWORK = 1222& Const ERROR_NOT_CONNECTED = 2250& Const NO_ERROR = 0 Sub GetFileProperties() Dim sDriveSpec As String Set objFSO = CreateObject("Scripting.FileSystemObject") Set objShell = CreateObject("Shell.Application") myFiles = Application.GetOpenFilename("All file types,*.*", , , , True) If Not IsArray(myFiles) Then Exit Sub j = Cells.SpecialCells(xlCellTypeLastCell).Row + 1 For Each myFile In myFiles i = InStrRev(myFile, "\") sFileName = Mid(myFile, i + 1) sFolderPathspec = Left(myFile, i) sDriveSpec = Left(sFolderPathspec, 2) Set objFolder = objShell.NameSpace(sFolderPathspec) Cells(j, 1).Value = UNCPath(sDriveSpec) & Mid(sFolderPathspec, 3) For i = 0 To 40 Cells(j, i + 2).Value = objFolder.GetDetailsOf(objFolder.ParseName(sFileName), i) Next j = j + 1 Next End Sub Function UNCPath(strDriveLetter As String) As String Dim lpszLocalName As String Dim lpszRemoteName As String Dim cbRemoteName As Long lpszLocalName = strDriveLetter lpszRemoteName = String$(255, Chr$(32)) cbRemoteName = Len(lpszRemoteName) lngreturn = WNetGetConnection(lpszLocalName, lpszRemoteName, cbRemoteName) If lngreturn = NO_ERROR Then UNCPath = Trim$(Replace$(lpszRemoteName, Chr$(0), "")) Else UNCPath = strDriveLetter End If End Function

TobiF
TobiF

To me, this code seems more straightforward (one more directly relying on built-in VBA functionality for browsing files) than the suggestions in a subthread higher up. But I have one question about these two lines: If Left(d_Dir, 1) "\" Then d_Dir = d_Dir & "\" Here you test if the string lacks a backslash in the BEGINNING of the string. If that is the case, then you add a backslash TO THE END of the string. Is that intentional?

Willysaef
Willysaef

I will use the CSV format rather than the txt format as the output file. The CSV format is the same as TXT, as every line will make a row, and every comma will start a new column. C:\>DIR /S /B > Out.csv open the Out.CSV file in Excel. :)

TobiF
TobiF

1. Open command window in needed folder (With powertoys on the system, it's just a right click in the folder tree) 2. Enter command dir /b > dirlist.txt 3. Open dirlist.txt in notepad and copy what's needed into Excel. (Note that the file dirlist.txt itself will also be listed, you may want to delete it) /b gives just the file names, without dates and sizes.

Editor's Picks