{"id":54633,"date":"2009-01-12T11:54:00","date_gmt":"2009-01-12T11:54:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/01\/12\/hey-scripting-guy-how-can-i-add-a-function-to-an-office-excel-spreadsheet\/"},"modified":"2009-01-12T11:54:00","modified_gmt":"2009-01-12T11:54:00","slug":"hey-scripting-guy-how-can-i-add-a-function-to-an-office-excel-spreadsheet","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-add-a-function-to-an-office-excel-spreadsheet\/","title":{"rendered":"Hey, Scripting Guy! How Can I Add a Function to an Office Excel Spreadsheet?"},"content":{"rendered":"

\"Hey, <\/h2>\n

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?<\/p>\n

– EP<\/p>\n

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

Hi EP,<\/p>\n

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!) <\/p>\n

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.<\/p>\n\n<\/thead>\n\n\n
\n

The Office Excel automation model is documented here on MSDN<\/a>. Here are previous “Hey, Scripting Guy!” articles<\/a> that illustrate using VBScript to automate Microsoft Excel. The Office space archive<\/a> also has numerous examples of using VBScript with Office Excel. You can also find lots of examples of automating Office Excel in the Script Center Repository<\/a>. Perhaps I also saved the best for last. The Community-Submitted Scripts Center has a ton<\/i> of scripts that automate Office Excel. Here is the direct link to that gold mine of information<\/a>. Look in the Microsoft Office section. If you are new to using Microsoft PowerShell, you can get a jump-start on it with the resources in the Windows PowerShell hub<\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

Here is today’s script:<\/p>\n

Function New-Excel($sheetName, $myData)\n{\n $excel = new-object -comobject excel.application\n $excel.visible = $true\n $workbook = $excel.workbooks.add()\n $workbook.workSheets.item(3).delete()\n $workbook.WorkSheets.item(2).delete()\n $workbook.WorkSheets.item(1).Name = $sheetName\n $sheet = $workbook.WorkSheets.Item($sheetName)\n New-SheetHeader $sheetName $myData\n} #end New-Excel\nFunction New-SheetHeader($sheetName, $myData)\n{\n $x=2\n $sheet.cells.item(1,1) = $sheetName\n Foreach($data in $mydata)\n  {\n   $sheet.cells.item($x, 1) = $data\n   $x++\n  } #end foreach\n  New-Range $sheet\n} #end New-SheetHeader\nFunction New-Range($sheet)\n{\n $range = $sheet.usedRange\n $maxRows = $range.rows.count\n $rangeString = $range.address().tostring() -replace \"\\$\",''\n New-Function $sheet $maxRows $rangeString\n} #end New-Range\nFunction New-Function($sheet,$maxRows,$rangeString)\n{\n $functions = $excel.WorkSheetfunction\n $sheet.cells.item($maxRows+1,1) = $functions.sum($range)\n $sheet.cells.item($maxRows+1,2) = \"Sum $rangeString\"\n} #end New-Function\n# *** entry point to script ***\n$myData = 1,2,3,4,5\n$sheetName = \"My Data\"\nNew-Excel $sheetName $myData\n<\/pre>\n
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<\/b> function. When calling the New-Excel<\/b> 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<\/b> keyword. This is seen here:<\/p>\n
Function New-Excel($sheetName, $myData)<\/pre>\n
Just like in VBScript we need to create an instance of the Excel.Application<\/b> object. The Excel.Application<\/b> 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:<\/p>\n
$excel.visible = $true<\/pre>\n
Now we want to add a workbook. After we have the workbook<\/b> object, we can use the worksheets<\/b> property to obtain a collection of worksheet<\/b> objects, and then use the item<\/b> 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:<\/p>\n
$workbook = $excel.workbooks.add()\n$workbook.workSheets.item(3).delete()\n$workbook.WorkSheets.item(2).delete()\n<\/pre>\n
It is now a good idea to rename the remaining worksheet, because the name is used to create the worksheet<\/b> 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<\/b> name. To name the worksheet, you assign a value to the name property of the worksheet<\/b> object. We use the number 1<\/b> to refer to the first worksheet in the collection of worksheets. This is seen here:<\/p>\n
$workbook.WorkSheets.item(1).Name = $sheetName<\/pre>\n
We now store the worksheet in a variable called $sheet<\/b>. We will be using this later. We also call the next<\/b> function, which is the New-SheetHeader<\/b> function. This is seen here: <\/p>\n
$sheet = $workbook.WorkSheets.Item($sheetName)\n New-SheetHeader $sheetName $myData\n<\/pre>\n
The complete New-Excel<\/b> function is seen here: <\/p>\n
Function New-Excel($sheetName, $myData)\n{\n $excel = new-object -comobject excel.application\n $excel.visible = $true\n $workbook = $excel.workbooks.add()\n $workbook.workSheets.item(3).delete()\n $workbook.WorkSheets.item(2).delete()\n $workbook.WorkSheets.item(1).Name = $sheetName\n $sheet = $workbook.WorkSheets.Item($sheetName)\n New-SheetHeader $sheetName $myData\n} #end New-Excel\n<\/pre>\n
In the New-SheetHeader<\/b> function we use the cells property<\/b> to return a collection of cell objects, and we use the item<\/b> 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: <\/p>\n
$sheet.cells.item(1,1) = $sheetName<\/pre>\n
We then start at the second row, and add each piece of data to a new cell. The $x=2<\/b> construction is used to control our starting position. The $x++<\/b> construction increments the value of $x<\/b> by one on each loop through the collection of data that is stored in the $mydata<\/b> variable. To do this we use the foreach<\/b> statement, as seen here: <\/p>\n
Foreach($data in $mydata)\n  {\n   $sheet.cells.item($x, 1) = $data\n   $x++\n  } #end foreach\n<\/pre>\n
The complete New-SheetHeader<\/b> function is seen here:<\/p>\n
Function New-SheetHeader($sheetName, $myData)\n{\n $x=2\n $sheet.cells.item(1,1) = $sheetName\n Foreach($data in $mydata)\n  {\n   $sheet.cells.item($x, 1) = $data\n   $x++\n  } #end foreach\n  New-Range $sheet\n} #end New-SheetHeader\n<\/pre>\n
The New-Range<\/b> function is used to create a range. To create our range, we query the usedrange<\/b> property from the worksheet object that is stored in the $sheet<\/b> variable. We use the rows<\/b> property from the range<\/b> object to return a rows<\/b> collection. The rows<\/b> collection has a count<\/b> property that tells us how many rows are in the range. We store this value in the $maxRows<\/b> variable. This is seen here: <\/p>\n
$range = $sheet.usedRange\n$maxRows = $range.rows.count\n<\/pre>\n
We want to know the range of cells that is used by the range<\/b> object. To find this, we call the address<\/b> method and turn it into a string by using the tostring<\/b> method. The result looks like this: $a1:$a6<\/b>. While this is readable, it would be better without the dollar sign in front of it. We use the replace<\/b> 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<\/b> 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<\/b> 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: <\/p>\n
rangeString = $range.address().tostring() -replace \"\\$\",''<\/pre>\n
The next thing we do is call the New-Function<\/b> function and pass it the sheet<\/b> object contained in the $sheet<\/b> variable, the number of maximum rows in the range, and the range of used cells in string fashion. The completed New-Range<\/b> function is seen here: <\/p>\n
Function New-Range($sheet)\n{\n $range = $sheet.usedRange\n $maxRows = $range.rows.count\n $rangeString = $range.address().tostring() -replace \"\\$\",''\n New-Function $sheet $maxRows $rangeString\n} #end New-Range\n<\/pre>\n
We use the New-Function<\/b> function to add our function to the spreadsheet. The first thing we need to do is create a WorkSheetfunction<\/b> object. To do this, we query the WorkSheetfunction<\/b> property from the Excel application object. This is shown here: <\/p>\n
$functions = $excel.WorkSheetfunction<\/pre>\n
After we have the WorkSheetfunction<\/b> 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<\/b> function, so we give it our range<\/b> 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<\/b> object, with the item<\/b> method. The first value of the item<\/b> method refers to the row, and the second value to the column. This is seen here: <\/p>\n
$sheet.cells.item($maxRows+1,1) = $functions.sum($range)<\/pre>\n
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<\/b> method to refer to the cell beside the one containing the sum<\/b> function. This is shown here: <\/p>\n
$sheet.cells.item($maxRows+1,2) = \"Sum $rangeString\"<\/pre>\n
The completed New-Function<\/b> function is shown here:<\/p>\n
Function New-Function($sheet,$maxRows,$rangeString)\n{\n $functions = $excel.WorkSheetfunction\n $sheet.cells.item($maxRows+1,1) = $functions.sum($range)\n $sheet.cells.item($maxRows+1,2) = \"Sum $rangeString\"\n} #end New-Function\n<\/pre>\n
The entry point is much less dramatic than the previous code. It contains the data that is stored in the $myData<\/b> variable and the sheet name, and then it calls the function. This is seen here: <\/p>\n
# *** entry point to script ***\n$myData = 1,2,3,4,5\n$sheetName = \"My Data\"\nNew-Excel $sheetName $myData\n<\/pre>\n
After the script is run, the following spreadsheet is produced:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
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.<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/b><\/span><\/span><\/p>\n
<\/b><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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,48,49,3,45],"class_list":["post-54633","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54633","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=54633"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54633\/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=54633"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54633"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54633"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}