{"id":67023,"date":"2006-06-27T15:32:00","date_gmt":"2006-06-27T15:32:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/06\/27\/how-can-i-copy-a-file-numerous-times-and-rename-each-of-them-from-a-list-in-excel\/"},"modified":"2006-06-27T15:32:00","modified_gmt":"2006-06-27T15:32:00","slug":"how-can-i-copy-a-file-numerous-times-and-rename-each-of-them-from-a-list-in-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-copy-a-file-numerous-times-and-rename-each-of-them-from-a-list-in-excel\/","title":{"rendered":"How Can I Copy a File Numerous Times and Rename Each of Them from a List in Excel?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I copy a file 150 times and then rename each of those copies using a list of names found in an Excel spreadsheet?

— DF<\/P>\"Spacer\"\"Hey,\"TechNet<\/A> \n

Hey, DF. You know, when we first started this column 2 years ago (must be time to mention the 500<\/B>th<\/SUP> Hey, Scripting Guy! celebration<\/B><\/A> again) our biggest concern was this: what are we going to do if no one ever writes in with a question? We had stockpiled a few questions, enough to get us through the first month, but after those first 30 days, then what? <\/P>\n

Well, if you\u2019ve ever wondered just how clueless the Scripting Guys really are, let\u2019s put it this way: nowadays this column receives 20 or more questions each and every day. For those of you who occasionally write in asking why we haven\u2019t answered your question, well, that\u2019s probably why: to say that we have a backlog of questions waiting to be answered would be a bit of an understatement.<\/P>\n

So then why did we answer DF\u2019s question? No reason, really; it\u2019s just luck of the draw more than anything else. OK, sure, DF did<\/I> add the following P.S. to his email, but the Scripting Guys aren\u2019t swayed by such a blatant attempt to curry their favor:<\/P>\n

\u201cI love the column. It\u2019s one of my daily tasks to visit the site and see what you are doing that day.\u201d<\/P>\n

Sorry, DF: the Scripting Guys can\u2019t be bribed. (Well, actually we can easily be bribed<\/I>; we just can\u2019t be flattered.)<\/P>\n

By astonishing coincidence, however, another reader named DF asked us the exact same question. And since this other DF didn\u2019t try to butter us up we decided it would be OK to answer his or her question. Just as long as we didn\u2019t answer the first DF\u2019s question; after all, the professional integrity of the Hey, Scripting Guy!<\/I> column is at stake here.<\/P>\n

Or at least it would be if the column actually had<\/I> any professional integrity. But that\u2019s beside the point.<\/P>\n

Here\u2019s the solution we came up with:<\/P>

strComputer = “.”<\/p>\n
Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n
Set colFiles = objWMIService. _\n    ExecQuery(“Select * From CIM_DataFile Where Name = ‘C:\\\\Scripts\\\\Test.txt'”)<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Names.xls”)<\/p>\n
intRow = 1<\/p>\n
Do Until objExcel.Cells(intRow,1).Value = “”\n    strNewName = objExcel.Cells(intRow, 1).Value<\/p>\n
    For Each objFile in colFiles\n        strCopy = “C:\\Scripts\\” & strNewName\n        objFile.Copy(strCopy)\n    Next<\/p>\n
    intRow = intRow + 1\nLoop<\/p>\n
objExcel.Quit\n<\/PRE>\n
We start out with some basic WMI code that binds us to the file we want to copy. To do that, we assign the value dot (.) to a variable named strComputer; in a minute we\u2019ll use this variable to connect to the WMI service on the local computer. What\u2019s that? What if you want to bind to the WMI service (and to a file) stored on a remote computer? No problem; just assign the variable strComputer the name of that remote machine:<\/P>
strComputer = “atl-fs-01”\n<\/PRE>\n
After we connect to the WMI service we then use this line of code to bind to the file C:\\Scripts\\Test.txt:<\/P>
Set colFiles = objWMIService. _\n    ExecQuery(“Select * From CIM_DataFile Where Name = ‘C:\\\\Scripts\\\\Test.txt'”)\n<\/PRE>\n
No, we haven\u2019t been drinking (why does everyone always assume that?); we really do<\/I> need to indicate the path as C:\\\\Scripts\\\\Test.txt. That\u2019s because the \\ is a reserved character in WMI; one of the consequences of being a reserved character is that any time we use the \\ in a Where clause we need to \u201cescape\u201d the character, which simply means we need to preface any \\ in the path with a second \\. Thus, C:\\Scripts\\Test.txt ends up as C:\\\\Scripts\\\\Test.txt.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. In all honesty, the Scripting Guys never take a good, stiff drink before writing this column. However, we have heard that some people take a good, stiff drink before reading<\/I> this column. But that\u2019s nothing to be ashamed of; our editor has to do that, too.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After binding to Test.txt (which, we should probably point out, is the file we\u2019re going to copy 150 times) we use these lines of code to create a visible instance of Microsoft Excel and open the spreadsheet C:\\Scripts\\Names.xls (the Excel document that contains all our new file names):<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Names.xls”)\n<\/PRE>\n
Now it\u2019s time to take a good, stiff drink and get to work.<\/P>\n
Um, by which we mean now it\u2019s time to \u2026 roll up our sleeves \u2026 and get to work.<\/P>\n
To begin with, we assign the value 1 to a variable named intRow; we\u2019re going to use this variable to keep track of the current row in the spreadsheet. Speaking of which, we\u2019re assuming this spreadsheet simply lists file names in column A, that the worksheet does not have a header row, and that there are no blank spaces anywhere in the list. If that\u2019s not the case, you\u2019ll probably need to modify the code for navigating through the spreadsheet. For information on how you might do that, check out the Office Space archive<\/B><\/A>.<\/P>\n
Our next step is to set up a Do Until loop that runs until we encounter a blank cell in column A; that loop looks like this:<\/P>
Do Until objExcel.Cells(intRow,1).Value = “”\n<\/PRE>\n
As you can see, we use the variable intRow to indicate the cell row; because intRow is initially assigned the value 1 that means we\u2019re looking at cell row 1, column 1 (or cell A1). If this cell is blank we exit the loop. <\/P>\n
But what it the cell isn\u2019t<\/I> blank? Well, in that case we assign the value of the cell to a variable named strNewName (as you recall, the spreadsheet is simply a list of new file names, like Test001.txt, Test002.txt, etc.):<\/P>
strNewName = objExcel.Cells(intRow, 1).Value\n<\/PRE>\n
That brings us to this block of code:<\/P>
For Each objFile in colFiles\n    strCopy = “C:\\Scripts\\” & strNewName\n    objFile.Copy(strCopy)\nNext\n<\/PRE>\n
What we have here is a For Each loop that walks through the collection of items returned by our WMI query. Granted, there can only be, at most, one file in this collection, the file C:\\Scripts\\Test.txt. However, even a one-item collection is still a collection, which is why we need the For Each loop. Inside that loop we use this line of code to construct a path for this first file copy:<\/P>
strCopy = “C:\\Scripts\\” & strNewName\n<\/PRE>\n
We should note that this line of code might not be needed; if your Excel spreadsheet features full file paths (C:\\Scripts\\Test001.txt) instead of just file names (Test001.txt) then you don\u2019t need to manually create the path. Because DF – that is, because the second<\/I> DF – specifically said file names<\/I>, we decided to play it safe and construct a path consisting of the folder – C:\\Scripts\\<\/B> — and the file name.<\/P>\n
Why do we even need<\/I> a complete path? There\u2019s a good reason for that: we\u2019re about to copy the file using WMI, and WMI\u2019s Copy<\/B> method requires a complete file path, even if you\u2019re copying the file to the same folder as the target file.<\/P>\n
Speaking of WMI\u2019s Copy method, in our very next line of code we use that method to copy the file Test.txt to the new file:<\/P>
objFile.Copy(strCopy)\n<\/PRE>\n
As you can see, there\u2019s nothing much to this: we simply call the Copy method, passing as the sole parameter the path to the new file. When we finish with this call we\u2019ll have two identical files: C:\\Scripts\\Test.txt and C:\\Scripts\\Test001.txt (or whatever file name we had in cell A1 of our spreadsheet). We increment the variable intRow by 1 and then loop around and repeat this process with the next name\/cell in the spreadsheet.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. We knew that someone<\/I> would ask that: we increment intRow by 1 simply because this is the variable we use to determine the row in the spreadsheet. Having just taken care of row 1, we now need to move down to row 2. The only way to do that? Make sure that intRow is now equal to 2.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
Eventually, we\u2019ll have copied Test.txt 150 times, once for each entry in the spreadsheet. Of course, one of the nice things about this script is that you aren\u2019t limited to making exactly 150 copies. Instead, the script will make as many copies as there are file names listed in the spreadsheet. <\/P>\n
So there you have it, DF. No, not you, the, uh, other<\/I> DF \u2026. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. OK, we admit it: all kidding aside we really did appreciate DF\u2019s – the real<\/I> DF\u2019s – note; it\u2019s nice to know that people find this column useful. Thanks, DF. That said, we still can\u2019t guarantee that we\u2019ll ever be able answer every question that comes our way. However, there might be a way to answer at least some of those – hold on a second while we distract our editor. Say, isn\u2019t that a dangling participle over there?<\/P>\n
OK, she\u2019s gone. Now, didn\u2019t we hear something about bribery .\u2026<\/P>\n
Editor\u2019s Note: She\u2019s back, and hasn\u2019t had nearly enough to drink to let that get by. In case there are any lawyers out there reading this – and we know this is a very popular column with the lawyers – we were just kidding, the Scripting Guys do not accept bribes. Or if they do the editor knows nothing about it<\/I><\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I copy a file 150 times and then rename each of those copies using a list of names found in an Excel spreadsheet?— DF Hey, DF. You know, when we first started this column 2 years ago (must be time to mention the 500th Hey, Scripting Guy! celebration again) our […]<\/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":[710,711,48,3,5,6],"class_list":["post-67023","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-scripting-guy","tag-vbscript","tag-wmi"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I copy a file 150 times and then rename each of those copies using a list of names found in an Excel spreadsheet?— DF Hey, DF. You know, when we first started this column 2 years ago (must be time to mention the 500th Hey, Scripting Guy! celebration again) our […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67023","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=67023"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67023\/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=67023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}