How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?

ScriptingGuy1

Hey, Scripting Guy! How can I rename a bunch of folders using a script? The old folder names are in the first column of an Excel spreadsheet and the new folder names are in the second column of an Excel spreadsheet.

— SF

Hey, SF. Oh, and welcome to Hey, Scripting Guy!, your daily source for scripting information. We’d like you to know that Hey, Scripting Guy!, unlike so many other daily scripting columns, is never outsourced; instead, the Scripting Guys create each column themselves, right here in the US of A. Hey, Scripting Guy!, a symbol of American pride and cratfmanshpi.

Uh, craftsmanship.

At any rate, thanks for the question, SF. Could you have found a more reputable and reliable source for scripting information? Probably. But seeing as how you didn’t find a more reputable and reliable source for scripting information we’ll see if we can help. Can we come up with a script that can take information from an Excel spreadsheet and then use that information to rename a bunch of folders? We’re about to find out.

Let’s start by taking a peek at our spreadsheet, a very simple affair in which – as you noted, SF – the old (existing) folder names are in column 1 and the corresponding new folder names are in column 2:

In other words, we currently have a folder named C:\Scripts\Test Folder 1; when we’re done, we want this folder to have a new name: C:\Scripts\Active Directory. How are we going to do that? Like this:

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
objExcel.Visible = True
i = 1
Do Until objExcel.Cells(i, 1).Value = “”
    strOldFolderName = objExcel.Cells(i, 1).Value
    strNewFolderName = objExcel.Cells(i, 2).Value
    strOldFolderName = Replace(strOldFolderName, “\”, “\\”)
    Set colFolders = objWMIService.ExecQuery _
        (“Select * from Win32_Directory where Name = ‘” & strOldFolderName & “‘”)
    For Each objFolder in colFolders
        errResults = objFolder.Rename(strNewFolderName)
    Next
    i = i + 1
Loop

As you can see, we start out by connecting to the WMI service on the local computer. What’s nice about this script (and nearly all WMI scripts, for that matter) is that we aren’t limited to working with the local machine. Want to rename folders on, say, the remote computer atl-fs-01? No problem; just assign the name of that remote computer to the variable strComputer, like so:

strComputer = “atl-fs-01”

After connecting to the WMI service we then use this block of code to create an instance of the Excel.Application object, open the spreadsheet C:\Scripts\Test.xls, and then make this instance of Excel visible onscreen:

Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
objExcel.Visible = True

And no, Excel doesn’t have to be visible onscreen in order for this script to work; we make the application visible just so you can see what’s going on. If you’d just as soon have Excel run in a hidden window then remove the line of code that sets the Visible property to True. Oh, and be sure and add code like this at the end of the script to make sure Excel quits when the script is finished:

objExcel.Quit

With Excel up and running we next set the value of a counter variable named i to 1; we’ll use this variable to keep track of the current row in the spreadsheet. That brings us to the following Do Until loop:

Do Until objExcel.Cells(i, 1).Value = “”

As you can see, with this loop we’re checking the value of a cell in the spreadsheet. Which cell? Well, for starters, we’re checking the cell in row 1 (remember, the counter variable i is equal to 1), column 1. And what we’re doing is checking to see whether or not the cell is empty (that is, whether or not the Value is equal to “”). If the cell is empty that means we’ve run out of folder names. In that case we exit the loop and the script terminates.

If the cell isn’t empty we assume that’s because there’s a folder name listed there. With that in mind we then execute the following two lines of code. The first line stores the current folder name in the variable strOldFolderName; the second line looks at cell i, 2 (row 1, column 2) to get the new folder name, a value that is stashed in the variable strNewFolderName:

strOldFolderName = objExcel.Cells(i, 1).Value
strNewFolderName = objExcel.Cells(i, 2).Value

Among other things, that means strOldFolderName is now equal to C:\Scripts\Test Folder 1. Believe it or not, that’s a problem. Why? Well, in a minute or two we’re going to use strOldFolderName in a WMI query; that’s a problem because the \ is a reserved character in WMI, and any \’s that show up in a query will trigger an error. Therefore, we need to use the following line of code to “escape” each \, a process which simply means prefacing each \ with a second \:

strOldFolderName = Replace(strOldFolderName, “\”, “\\”)

That makes strOldFolderName equal to C:\\Scripts\\Test Folder 1. That looks weird, but it can now be used in a WMI query.

Speaking of WMI queries, our next line of code just happens to be a WMI query, one that asks for a collection of all the folders on the computer that have a Name (path) equal to strOldFolderName:

Set colFolders = objWMIService.ExecQuery _
    (“Select * from Win32_Directory where Name = ‘” & strOldFolderName & “‘”)

All we have to do now is set up a For Each loop to loop through all the folders in the collection (and, because folder paths must be unique on a computer, the collection will contain, at most, a single item). Inside that loop we use this line of code to take the existing folder and rename it to the folder path specified by the variable strNewFolderName:

errResults = objFolder.Rename(strNewFolderName)

After calling the Rename method we increment the value of our counter variable by 1, then loop around and repeat the process with the new row in the spreadsheet. When we’re all done each of the 5 folders listed in the spreadsheet will have shiny new names. Just like we wanted them to.

Incidentally, it is true that the Scripting Guys write this column themselves; unlike other high-tech entrepreneurs we never outsource our work to developing countries. Not that we didn’t try, mind you; we just couldn’t find any developing country desperate enough (or silly enough) to want to write a daily scripting column.

ScriptingGuy1

Follow