Business and Technology Consulting

Using Excel to Automate File Management

You feel comfortable using Excel to create significant models, but when it comes to manipulating files, you're still dragging, dropping, pointing, and clicking. Suppose you needed to rename all of the files in a directory. With your Excel expertise, and some DOS commands, you can eliminate the one-at-a-time approach required by Explorer.

In a DOS window:

1. change to the desired directory (cd \mydirectory)

2. capture all of the filenames into a file (dir >myfile.txt)

In Excel:

1. Open your file (e.g. myfile.txt) and use your Excel wizardry to manipulate the result so that you have two columns with the old name (e.g. column A) and the new name (e.g. column B). 

2. Add a third column with the formula:  ="ren"&A1&" "&B1

3. Copy cell C1 for all rows

4. Copy column C and paste special-values only into a new sheet

5. Save the new sheet as a text file with the extension .bat (e.g. ChangeNames.bat)

Go back to the DOS window, type the batch file name (e.g. ChangeNames), and watch it fly! When you have a lot of files, this is more efficient than one-at-a-time renames. This technique works with any command-line process. After you understand how to implement it for one, you can repeat it for all your rows in Excel.

(C)Copyright 2005, James T. Moran & Associates. All rights reserved.