How Can I Copy a File Numerous Times and Rename Each of Them from a List in Excel?

ScriptingGuy1

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 500^th Hey, Scripting Guy! celebration 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?

Well, if you’ve ever wondered just how clueless the Scripting Guys really are, let’s 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’t answered your question, well, that’s probably why: to say that we have a backlog of questions waiting to be answered would be a bit of an understatement.

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

“I love the column. It’s one of my daily tasks to visit the site and see what you are doing that day.”

Sorry, DF: the Scripting Guys can’t be bribed. (Well, actually we can easily be bribed; we just can’t be flattered.)

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

Or at least it would be if the column actually had any professional integrity. But that’s beside the point.

Here’s the solution we came up with:

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFiles = objWMIService. _
    ExecQuery(“Select * From CIM_DataFile Where Name = ‘C:\\Scripts\\Test.txt'”)
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Names.xls”)
intRow = 1
Do Until objExcel.Cells(intRow,1).Value = “”
    strNewName = objExcel.Cells(intRow, 1).Value
    For Each objFile in colFiles
        strCopy = “C:\Scripts\” & strNewName
        objFile.Copy(strCopy)
    Next
    intRow = intRow + 1
Loop
objExcel.Quit

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’ll use this variable to connect to the WMI service on the local computer. What’s 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:

strComputer = “atl-fs-01”

After we connect to the WMI service we then use this line of code to bind to the file C:\Scripts\Test.txt:

Set colFiles = objWMIService. _
    ExecQuery(“Select * From CIM_DataFile Where Name = ‘C:\\Scripts\\Test.txt'”)

No, we haven’t been drinking (why does everyone always assume that?); we really do need to indicate the path as C:\\Scripts\\Test.txt. That’s 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 “escape” 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.

After binding to Test.txt (which, we should probably point out, is the file we’re 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):

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Names.xls”)

Now it’s time to take a good, stiff drink and get to work.

Um, by which we mean now it’s time to … roll up our sleeves … and get to work.

To begin with, we assign the value 1 to a variable named intRow; we’re going to use this variable to keep track of the current row in the spreadsheet. Speaking of which, we’re 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’s not the case, you’ll 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.

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:

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

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’re looking at cell row 1, column 1 (or cell A1). If this cell is blank we exit the loop.

But what it the cell isn’t 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.):

strNewName = objExcel.Cells(intRow, 1).Value

That brings us to this block of code:

For Each objFile in colFiles
    strCopy = “C:\Scripts\” & strNewName
    objFile.Copy(strCopy)
Next

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:

strCopy = “C:\Scripts\” & strNewName

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’t need to manually create the path. Because DF – that is, because the second DF – specifically said file names, we decided to play it safe and construct a path consisting of the folder – C:\Scripts\ — and the file name.

Why do we even need a complete path? There’s a good reason for that: we’re about to copy the file using WMI, and WMI’s Copy method requires a complete file path, even if you’re copying the file to the same folder as the target file.

Speaking of WMI’s Copy method, in our very next line of code we use that method to copy the file Test.txt to the new file:

objFile.Copy(strCopy)

As you can see, there’s 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’ll 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.

Eventually, we’ll 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’t limited to making exactly 150 copies. Instead, the script will make as many copies as there are file names listed in the spreadsheet.

So there you have it, DF. No, not you, the, uh, other DF ….

ScriptingGuy1

Follow