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’m having another music flashback. I am sitting here listening to The Kingston Trio singing “The Escape of Old John Webb,” and I’m 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, 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 that was written for VBScript. The Kingston Trio is over. I have grabbed my Zune, and turned on Vampire Weekend. Let’s update the script to Windows PowerShell.

Here’s today’s

Here’s today’s script:

$templateFile = "C:\fso\FoldersAndFilesTemplate.txt"
$excelSheet = "C:\fso\FoldersAndFiles.xls"
$excel = New-Object -comobject Excel.Application
$excel.visible = $false
$workbook = $excel.Workbooks.Open($excelSheet)
$worksheet = $workbook.sheets.item(1)
$rowMax = ($worksheet.usedRange.rows).count
$column = 1
For($row = 1 ; $row -le $rowMax ; $row ++)
{
 $fileName = $excel.cells.item($row,$column).value2
 $fileName += ".txt"
 "copying $fileName"
 Copy-Item -path $templateFile -Destination "C:\fso\fso1\$fileName"
}
$excel.quit()

Now the first thing we do is assign a string to the $templateFile variable for the path to the template file. This is seen here:

$templateFile = "C:\fso\FoldersAndFilesTemplate.txt"

The next thing we do is assign a string for the path to the Microsoft Excel spreadsheet. The $excelSheet variable is used to hold that path:

$excelSheet = "C:\fso\FoldersAndFiles.xls"

We next create the Excel.Application object. When we work with Microsoft Excel from within a script, we always need to create an instance of the Excel.Application object. This is a COM object and is the same one used in VBScript. We use the New-Object cmdlet with the –comobject parameter to create the object. We store the returned object in the $excel variable as seen here:

$excel = New-Object -comobject Excel.Application

After the Excel Application object has been created, we decide to make Excel invisible, so we set the visible property to false. 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):

$excel.visible = $false

If the Excel spreadsheet were made visible, you would see this spreadsheet:

Next we need to open the Excel workbook. To do this, we use the Open method from the Workbooks object. We store the returned workbook object in a variable named—surprisingly enough—$workbook. This is seen here:

$workbook = $excel.Workbooks.Open($excelSheet)

Once we have a workbook object, we can use the sheets property from the workbook 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:

$worksheet = $workbook.sheets.item(1)

We now need to find out how many rows are occupied in the spreadsheet. To do this, we use the usedRange property of the worksheet object to return a range object. We then use the rows property to return a rows collection from which we get the count. This is also something that was not done in the original script. The VBScript used a Do…Until loop and looked for a cell that had a value of “”. While that generally works, there is no reason to do that because we can use the usedRange property. This is seen here:

$rowMax = ($worksheet.usedRange.rows).count

We create a variable named $column 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 to refer to the column. This is shown here:

$column = 1

It is time to loop through the rows in the Excel spreadsheet. To do this, we are going to use a For statement. The For 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 variable is less than or equal to the value of $rowMax. The $rowMax 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 variable one at a time. This section of code is seen here:

For($row = 1 ; $row -le $rowMax ; $row ++)

Now we read the data file name that is stored in the cell. To do this, we use the item method of the cells collection from the Excel application object. We return the value of the value2 property and store it in the $filename variable. Next we add the extension .txt 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:

{
 $fileName = $excel.cells.item($row,$column).value2
 $fileName += ".txt"

Next we print out a message that we are copying the file, and then we use the copy-item cmdlet to copy the template file to the destination. This is seen here:

 "copying $fileName"
 Copy-Item -path $templateFile -Destination "C:\fso\fso1\$fileName"

Finally, we use the quit method to exit the Excel application. This last step is very important. If you do not call the quit 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. To call quit, you just type quit followed by empty parentheses as shown here:

$excel.quit()

Well, PR, it is still cold and foggy outside. Vampire Weekend is gone, and now it is Fado. I am listening to Mariza. 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!

Ed Wilson and Craig Liebendorfer, Scripting Guys