|
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.
|