{"id":66153,"date":"2006-10-30T15:29:00","date_gmt":"2006-10-30T15:29:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/10\/30\/how-can-i-use-information-in-an-excel-spreadsheet-to-rename-a-set-of-folders\/"},"modified":"2006-10-30T15:29:00","modified_gmt":"2006-10-30T15:29:00","slug":"how-can-i-use-information-in-an-excel-spreadsheet-to-rename-a-set-of-folders","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-use-information-in-an-excel-spreadsheet-to-rename-a-set-of-folders\/","title":{"rendered":"How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?"},"content":{"rendered":"

\"Hey, \n

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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, SF. Oh, and welcome to Hey, Scripting Guy!<\/I>, your daily source for scripting information. We\u2019d like you to know that Hey, Scripting Guy!<\/I>, 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!<\/I>, a symbol of American pride and cratfmanshpi.<\/P>\n

Uh, craftsmanship.<\/P>\n

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\u2019t<\/I> find a more reputable and reliable source for scripting information we\u2019ll 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\u2019re about to find out.<\/P>\n

Let\u2019s start by taking a peek at our spreadsheet, a very simple affair in which \u2013 as you noted, SF \u2013 the old (existing) folder names are in column 1 and the corresponding new folder names are in column 2:<\/P>\"Microsoft \n


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

strComputer = “.”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nobjExcel.Visible = True<\/p>\n
i = 1<\/p>\n
Do Until objExcel.Cells(i, 1).Value = “”\n    strOldFolderName = objExcel.Cells(i, 1).Value\n    strNewFolderName = objExcel.Cells(i, 2).Value\n    strOldFolderName = Replace(strOldFolderName, “\\”, “\\\\”)<\/p>\n
    Set colFolders = objWMIService.ExecQuery _\n        (“Select * from Win32_Directory where Name = ‘” & strOldFolderName & “‘”)<\/p>\n
    For Each objFolder in colFolders\n        errResults = objFolder.Rename(strNewFolderName)\n    Next<\/p>\n
    i = i + 1\nLoop\n<\/PRE>\n
As you can see, we start out by connecting to the WMI service on the local computer. What\u2019s nice about this script (and nearly all WMI scripts, for that matter) is that we aren\u2019t 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:<\/P>
strComputer = “atl-fs-01”\n<\/PRE>\n
After connecting to the WMI service we then use this block of code to create an instance of the Excel.Application<\/B> object, open the spreadsheet C:\\Scripts\\Test.xls, and then make this instance of Excel visible onscreen:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nobjExcel.Visible = True\n<\/PRE>\n
And no, Excel doesn\u2019t have<\/I> to be visible onscreen in order for this script to work; we make the application visible just so you can see what\u2019s going on. If you\u2019d just as soon have Excel run in a hidden window then remove the line of code that sets the Visible<\/B> 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:<\/P>
objExcel.Quit\n<\/PRE>\n
With Excel up and running we next set the value of a counter variable named i <\/I>to 1; we\u2019ll use this variable to keep track of the current row in the spreadsheet. That brings us to the following Do Until loop:<\/P>
Do Until objExcel.Cells(i, 1).Value = “”\n<\/PRE>\n
As you can see, with this loop we\u2019re checking the value of a cell in the spreadsheet. Which cell? Well, for starters, we\u2019re checking the cell in row 1 (remember, the counter variable i<\/I> is equal to 1), column 1. And what we\u2019re doing is checking to see whether or not the cell is empty (that is, whether or not the Value<\/B> is equal to \u201c\u201d). If the cell is<\/I> empty that means we\u2019ve run out of folder names. In that case we exit the loop and the script terminates.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Needless to say, if you use this approach you need to make sure there are no blank rows in your spreadsheet. If there are<\/I> blank rows, the first time the script encounters such a row it will assume that all the folders have been dealt with and exit the loop.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
If the cell isn\u2019t empty we assume that\u2019s because there\u2019s 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<\/I>, 2 (row 1, column 2) to get the new folder name, a value that is stashed in the variable strNewFolderName:<\/P>
strOldFolderName = objExcel.Cells(i, 1).Value\nstrNewFolderName = objExcel.Cells(i, 2).Value\n<\/PRE>\n
Among other things, that means strOldFolderName is now equal to C:\\Scripts\\Test Folder 1<\/B>. Believe it or not, that\u2019s a problem. Why? Well, in a minute or two we\u2019re going to use strOldFolderName in a WMI query; that\u2019s a problem because the \\ is a reserved character in WMI, and any \\\u2019s that show up in a query will trigger an error. Therefore, we need to use the following line of code to \u201cescape\u201d each \\, a process which simply means prefacing each \\ with a second \\:<\/P>
strOldFolderName = Replace(strOldFolderName, “\\”, “\\\\”)\n<\/PRE>\n
That makes strOldFolderName equal to C:\\\\Scripts\\\\Test Folder 1<\/B>. That looks weird, but it can now be used in a WMI query.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. We don\u2019t have to do anything to strNewFolderName; we can leave the value at C:\\Scripts\\Active Directory<\/B>. This variable won\u2019t be used in a query, which means we don\u2019t have to escape all instances of the \\ character.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Speaking of WMI queries, our next line of code just happens to be<\/I> a WMI query, one that asks for a collection of all the folders on the computer that have a Name<\/B> (path) equal to strOldFolderName:<\/P>
Set colFolders = objWMIService.ExecQuery _\n    (“Select * from Win32_Directory where Name = ‘” & strOldFolderName & “‘”)\n<\/PRE>\n
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:<\/P>
errResults = objFolder.Rename(strNewFolderName)\n<\/PRE>\n
After calling the Rename<\/B> 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\u2019re all done each of the 5 folders listed in the spreadsheet will have shiny new names. Just like we wanted them to.<\/P>\n
Incidentally, it is<\/I> 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\u2019t try, mind you; we just couldn\u2019t find any developing country desperate enough (or silly enough) to want to write a daily scripting column.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[711,11,48,49,3,12,5],"class_list":["post-66153","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-application","tag-folders","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-storage","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66153"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66153\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}