Hey, Scripting Guy! How Can I Add a Function to an Office Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! I need to add some data to an Excel spreadsheet. After I have done that, I would like to add a function to the bottom that will add up all the information in the columns above that. I have seen examples of plugging data into cells, but I do not know how many rows of data I will have. Also, I am not sure how to add a function. Can you help me?

– EP

Hi EP,

I imagine it would be rather useful to be able to add data to a Microsoft Office Excel spreadsheet and then perform calculations on that data. In fact, your e-mail message gave me an idea. I do an hour every day on the treadmill; however, I do not seem to achieve the same effort each day. To track myself, and to keep from being a slacker, I enter things such as time, total miles, total calories, and average heart rate, and then I enter it into an Excel spreadsheet, returning things such as average miles per hour and average calories per hour. Right now, I have to enter the data manually. If I could run a script that would take the numbers and automatically update the spreadsheet, it would be much easier. (And if I could write a script to run on the treadmill for me, it would be much much easier!)

Anyway, EP, this week we will be talking about using Windows PowerShell to automate Office Excel. We will use your question as a springboard for our discussions.

Here is today’s script:

Function New-Excel($sheetName, $myData)
{
 $excel = new-object -comobject excel.application
 $excel.visible = $true
 $workbook = $excel.workbooks.add()
 $workbook.workSheets.item(3).delete()
 $workbook.WorkSheets.item(2).delete()
 $workbook.WorkSheets.item(1).Name = $sheetName
 $sheet = $workbook.WorkSheets.Item($sheetName)
 New-SheetHeader $sheetName $myData
} #end New-Excel
Function New-SheetHeader($sheetName, $myData)
{
 $x=2
 $sheet.cells.item(1,1) = $sheetName
 Foreach($data in $mydata)
  {
   $sheet.cells.item($x, 1) = $data
   $x++
  } #end foreach
  New-Range $sheet
} #end New-SheetHeader
Function New-Range($sheet)
{
 $range = $sheet.usedRange
 $maxRows = $range.rows.count
 $rangeString = $range.address().tostring() -replace "\$",''
 New-Function $sheet $maxRows $rangeString
} #end New-Range
Function New-Function($sheet,$maxRows,$rangeString)
{
 $functions = $excel.WorkSheetfunction
 $sheet.cells.item($maxRows+1,1) = $functions.sum($range)
 $sheet.cells.item($maxRows+1,2) = "Sum $rangeString"
} #end New-Function
# *** entry point to script ***
$myData = 1,2,3,4,5
$sheetName = "My Data"
New-Excel $sheetName $myData

To make troubleshooting the script easier and to facilitate code reuse, the primary functionality of the script is created within a series of functions. Each of the function names follows the verb-noun pattern of Windows PowerShell cmdlets. In Windows PowerShell 2.0, tab expansion will work on user-defined function names, so it is a good idea to begin following that naming convention now. Let’s take a look at the New-Excel function. When calling the New-Excel function, we need to pass two values: the name of the spreadsheet to create and the data we are going to use. To create a function, you use the function keyword. This is seen here:

Function New-Excel($sheetName, $myData)

Just like in VBScript we need to create an instance of the Excel.Application object. The Excel.Application object is the main object that we use when working with the Excel automation model. After we have an instance of the application object, we use it to make Excel visible. Often you do not want to show Excel, but for demonstration purposes, it is good to have Excel appear. I often like to make it visible when writing scripts because I can see what is happening. This is shown here:

$excel.visible = $true

Now we want to add a workbook. After we have the workbook object, we can use the worksheets property to obtain a collection of worksheet objects, and then use the item method to delete a couple of the extra worksheets. I always delete the second and the third worksheets because I almost never use more than a single Excel worksheet, and by default Microsoft Excel always creates three worksheets in a workbook. This is seen here:

$workbook = $excel.workbooks.add()
$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

It is now a good idea to rename the remaining worksheet, because the name is used to create the worksheet object and will be used later to refer to the specific spreadsheet. So it makes sense to give it a useful name instead of the generic sheet1 name. To name the worksheet, you assign a value to the name property of the worksheet object. We use the number 1 to refer to the first worksheet in the collection of worksheets. This is seen here:

$workbook.WorkSheets.item(1).Name = $sheetName

We now store the worksheet in a variable called $sheet. We will be using this later. We also call the next function, which is the New-SheetHeader function. This is seen here:

$sheet = $workbook.WorkSheets.Item($sheetName)
 New-SheetHeader $sheetName $myData

The complete New-Excel function is seen here:

Function New-Excel($sheetName, $myData)
{
 $excel = new-object -comobject excel.application
 $excel.visible = $true
 $workbook = $excel.workbooks.add()
 $workbook.workSheets.item(3).delete()
 $workbook.WorkSheets.item(2).delete()
 $workbook.WorkSheets.item(1).Name = $sheetName
 $sheet = $workbook.WorkSheets.Item($sheetName)
 New-SheetHeader $sheetName $myData
} #end New-Excel

In the New-SheetHeader function we use the cells property to return a collection of cell objects, and we use the item method to grab the first cell in the first column. By assigning a value to the cell, we create our header. This is seen here:

$sheet.cells.item(1,1) = $sheetName

We then start at the second row, and add each piece of data to a new cell. The $x=2 construction is used to control our starting position. The $x++ construction increments the value of $x by one on each loop through the collection of data that is stored in the $mydata variable. To do this we use the foreach statement, as seen here:

Foreach($data in $mydata)
  {
   $sheet.cells.item($x, 1) = $data
   $x++
  } #end foreach

The complete New-SheetHeader function is seen here:

Function New-SheetHeader($sheetName, $myData)
{
 $x=2
 $sheet.cells.item(1,1) = $sheetName
 Foreach($data in $mydata)
  {
   $sheet.cells.item($x, 1) = $data
   $x++
  } #end foreach
  New-Range $sheet
} #end New-SheetHeader

The New-Range function is used to create a range. To create our range, we query the usedrange property from the worksheet object that is stored in the $sheet variable. We use the rows property from the range object to return a rows collection. The rows collection has a count property that tells us how many rows are in the range. We store this value in the $maxRows variable. This is seen here:

$range = $sheet.usedRange
$maxRows = $range.rows.count

We want to know the range of cells that is used by the range object. To find this, we call the address method and turn it into a string by using the tostring method. The result looks like this: $a1:$a6. While this is readable, it would be better without the dollar sign in front of it. We use the replace operator to replace the dollar sign with an empty character. The problem is that the dollar sign is a special character. If you are thinking it is used to designate variables, you are right, but that is not what is happening here. The replace operator uses regular expressions. In regular expressions, the dollar sign is used to mark the end of a match, and it is therefore necessary to escape the dollar sign to force the replace to see the dollar sign as a character instead of a special character. In Windows PowerShell, regular expressions use the backslash to escape a special character. This line of code is seen here:

rangeString = $range.address().tostring() -replace "\$",''

The next thing we do is call the New-Function function and pass it the sheet object contained in the $sheet variable, the number of maximum rows in the range, and the range of used cells in string fashion. The completed New-Range function is seen here:

Function New-Range($sheet)
{
 $range = $sheet.usedRange
 $maxRows = $range.rows.count
 $rangeString = $range.address().tostring() -replace "\$",''
 New-Function $sheet $maxRows $rangeString
} #end New-Range

We use the New-Function function to add our function to the spreadsheet. The first thing we need to do is create a WorkSheetfunction object. To do this, we query the WorkSheetfunction property from the Excel application object. This is shown here:

$functions = $excel.WorkSheetfunction

After we have the WorkSheetfunction object, we can choose any of the 160 built-in functions from Office Excel. Each function has its own signature, and this is where MSDN comes in very helpful. We want to use the sum function, so we give it our range object to have it add up all the values within our range of cells. We store this value in the first cell after the range of cells occupied by our data. When referring to a specific cell, you use the cells object, with the item method. The first value of the item method refers to the row, and the second value to the column. This is seen here:

$sheet.cells.item($maxRows+1,1) = $functions.sum($range)

We would also like to print out the range and a note tells us what the number really is. To do this, we use the item method to refer to the cell beside the one containing the sum function. This is shown here:

$sheet.cells.item($maxRows+1,2) = "Sum $rangeString"

The completed New-Function function is shown here:

Function New-Function($sheet,$maxRows,$rangeString)
{
 $functions = $excel.WorkSheetfunction
 $sheet.cells.item($maxRows+1,1) = $functions.sum($range)
 $sheet.cells.item($maxRows+1,2) = "Sum $rangeString"
} #end New-Function

The entry point is much less dramatic than the previous code. It contains the data that is stored in the $myData variable and the sheet name, and then it calls the function. This is seen here:

# *** entry point to script ***
$myData = 1,2,3,4,5
$sheetName = "My Data"
New-Excel $sheetName $myData

After the script is run, the following spreadsheet is produced:

Well, EP, great question, and a great way to kick off Office Excel week! See you tomorrow when we will build upon the techniques we established today.

Ed Wilson and Craig Liebendorfer, Scripting Guys

ScriptingGuy1

Follow