{"id":54703,"date":"2009-01-01T11:50:00","date_gmt":"2009-01-01T11:50:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/01\/01\/hey-scripting-guy-how-can-i-copy-a-file-multiple-times-and-give-a-unique-name-to-each-file\/"},"modified":"2009-01-01T11:50:00","modified_gmt":"2009-01-01T11:50:00","slug":"hey-scripting-guy-how-can-i-copy-a-file-multiple-times-and-give-a-unique-name-to-each-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-copy-a-file-multiple-times-and-give-a-unique-name-to-each-file\/","title":{"rendered":"Hey, Scripting Guy! How Can I Copy a File Multiple Times and Give a Unique Name to Each File?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! I have a file that I would like to copy a number of times. This file is basically a template. I have an Excel spreadsheet that contains a list of names that I would like to use for the newly copied files. Can you help me?<\/p>\n

– PR<\/p>\n

\"Spacer\"\"Hey,<\/p>\n

Hi PR,<\/p>\n

Sorry, I\u2019m having another music flashback. I am sitting here listening to The Kingston Trio<\/a> singing “The Escape of Old John Webb,” and I\u2019m sipping a cup of Constant Comment tea with a cinnamon stick in it (I do not like the harsher flavor of cassia). It is a rather cold and foggy day in Charlotte, North Carolina, USA<\/a>, and from my window I can see the trees shivering so much their leaves have left. Speaking of flashbacks, it seems we have had a question like this before. Let’s turn on the way-back machine, which is how we will find this article<\/a> that was written for VBScript. The Kingston Trio is over. I have grabbed my Zune, and turned on Vampire Weekend<\/a>. Let’s update the script to Windows PowerShell.<\/p>\n\n<\/thead>\n\n\n
\n

For information about working with files and folders in Windows PowerShell see this article<\/a>. You may also wish to see this section<\/a> of “What Can I Do with Windows PowerShell” that deals specifically with files and folders. The Windows PowerShell scripting hub<\/a> is a good place to get started with Windows PowerShell, and it includes download links and other items of use. The Sesame Script archive<\/a> has several VBScript articles dealing with files and folders. They are worth a look for VBScript examples. The community script repository has a good selection of files and folders scripts<\/a> in VBscript. You also may want to check the Hey, Scripting Guy! archive<\/a> for a number of VBScript examples of working with files and folders.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Here\u2019s today\u2019s <\/p>\n

Here\u2019s today\u2019s script:<\/p>\n

$templateFile = \"C:\\fso\\FoldersAndFilesTemplate.txt\"\n$excelSheet = \"C:\\fso\\FoldersAndFiles.xls\"\n$excel = New-Object -comobject Excel.Application\n$excel.visible = $false\n$workbook = $excel.Workbooks.Open($excelSheet)\n$worksheet = $workbook.sheets.item(1)\n$rowMax = ($worksheet.usedRange.rows).count\n$column = 1\nFor($row = 1 ; $row -le $rowMax ; $row ++)\n{\n $fileName = $excel.cells.item($row,$column).value2\n $fileName += \".txt\"\n \"copying $fileName\"\n Copy-Item -path $templateFile -Destination \"C:\\fso\\fso1\\$fileName\"\n}\n$excel.quit()\n<\/pre>\n
Now the first thing we do is assign a string to the $templateFile<\/b> variable for the path to the template file. This is seen here:<\/p>\n
$templateFile = \"C:\\fso\\FoldersAndFilesTemplate.txt\"<\/pre>\n
The next thing we do is assign a string for the path to the Microsoft Excel spreadsheet. The $excelSheet<\/b> variable is used to hold that path: <\/p>\n
$excelSheet = \"C:\\fso\\FoldersAndFiles.xls\"<\/pre>\n
We next create the Excel.Application<\/b> object. When we work with Microsoft Excel from within a script, we always need to create an instance of the Excel.Application<\/b> object. This is a COM object and is the same one used in VBScript. We use the New-Object<\/b> cmdlet with the \u2013comobject<\/b> parameter to create the object. We store the returned object in the $excel<\/b> variable as seen here: <\/p>\n
$excel = New-Object -comobject Excel.Application<\/pre>\n
After the Excel Application object has been created, we decide to make Excel invisible, so we set the visible property to false<\/b>. This is different than what was done in the original VBScript. In my testing, I found it rather annoying for Excel to jump up and steal the focus of the screen when I was interested in seeing the folders being created. In reality, there is no reason to make Excel visible, so I just make it invisible. This is seen here (if you feel you are missing out by not having Excel open, by all means make it visible):<\/p>\n
$excel.visible = $false<\/pre>\n
If the Excel spreadsheet were made visible, you would see this spreadsheet:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
Next we need to open the Excel workbook. To do this, we use the Open<\/b> method from the Workbooks<\/b> object. We store the returned workbook<\/b> object in a variable named\u2014surprisingly enough\u2014$workbook<\/b>. This is seen here:<\/p>\n
$workbook = $excel.Workbooks.Open($excelSheet)<\/pre>\n
Once we have a workbook<\/b> object, we can use the sheets<\/b> property from the workbook<\/b> object to reference the first spreadsheet. Since we were lazy and did not delete or even rename the extra spreadsheets, we need to use a number to reference the first worksheet. We do this here: <\/p>\n
$worksheet = $workbook.sheets.item(1)<\/pre>\n
We now need to find out how many rows are occupied in the spreadsheet. To do this, we use the usedRange<\/b> property of the worksheet<\/b> object to return a range<\/b> object. We then use the rows<\/b> property to return a rows<\/b> collection from which we get the count. This is also something that was not done in the original script. The VBScript used a Do\u2026Until<\/b> loop and looked for a cell that had a value of “”<\/b>. While that generally works, there is no reason to do that because we can use the usedRange<\/b> property. This is seen here: <\/p>\n
$rowMax = ($worksheet.usedRange.rows).count<\/pre>\n
We create a variable named $column<\/b> and set it equal to 1. This will be used to reference the first column. Even though I could just use 1 later on in the script, it makes the script easier to read if we use a variable such as $column<\/b> to refer to the column. This is shown here:<\/p>\n
$column = 1<\/pre>\n
It is time to loop through the rows in the Excel spreadsheet. To do this, we are going to use a For<\/b> statement. The For<\/b> statement begins with three parts. The first is where we are starting from. Here we are starting at 1. The second part is how far we are going. Here we are going until the value of the $row<\/b> variable is less than or equal to the value of $rowMax<\/b>. The $rowMax<\/b> variable is used to hold the maximum number of rows in the spreadsheet that are occupied. The last section is how we are going to get there. We are going to get there by incrementing the value of the $row<\/b> variable one at a time. This section of code is seen here:<\/p>\n
For($row = 1 ; $row -le $rowMax ; $row ++)<\/pre>\n
Now we read the data file name that is stored in the cell. To do this, we use the item<\/b> method of the cells<\/b> collection from the Excel application<\/b> object. We return the value of the value2<\/b> property and store it in the $filename<\/b> variable. Next we add the extension .txt<\/b> to the end of each file name. This is because it was easier to create a series of file names in Excel without having the file extension at the end. This is seen here: <\/p>\n
{\n $fileName = $excel.cells.item($row,$column).value2\n $fileName += \".txt\"\n<\/pre>\n
Next we print out a message that we are copying the file, and then we use the copy-item<\/b> cmdlet to copy the template file to the destination. This is seen here: <\/p>\n
 \"copying $fileName\"\n Copy-Item -path $templateFile -Destination \"C:\\fso\\fso1\\$fileName\"\n<\/pre>\n
Finally, we use the quit<\/b> method to exit the Excel application. This last step is very important. If you do not call the quit<\/b> method, you will end up with a new instance of the Excel process running on your computer each time you run the script. It would not take very many times before you consume all the resources on your computer and crash the thing! Also keep in mind that Microsoft Office products are not designed for server side automation<\/a>. To call quit<\/b>, you just type quit<\/b> followed by empty parentheses as shown here:<\/p>\n
$excel.quit()<\/pre>\n
Well, PR, it is still cold and foggy outside. Vampire Weekend is gone, and now it is Fado<\/a>. I am listening to Mariza<\/a>. I got addicted to Fado while I was in Lisbon for a month back in June. That is also where I got addicted to a cinnamon stick in my tea. See you tomorrow for Quick-Hits Friday. Paz!<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/b><\/span><\/font><\/p>\n
<\/b><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a file that I would like to copy a number of times. This file is basically a template. I have an Excel spreadsheet that contains a list of names that I would like to use for the newly copied files. Can you help me? – PR Hi PR, Sorry, I\u2019m […]<\/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":[38,3,12,45],"class_list":["post-54703","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-files","tag-scripting-guy","tag-storage","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a file that I would like to copy a number of times. This file is basically a template. I have an Excel spreadsheet that contains a list of names that I would like to use for the newly copied files. Can you help me? – PR Hi PR, Sorry, I\u2019m […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54703","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=54703"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54703\/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=54703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}