![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a><\/p>\nHey, MW. Could we ask you a favor: is there any chance that you could write back later with this question? As you may or may not know, the University of Washington men\u2019s basketball team is hosting a four-team, round-robin tournament this week. Sunday the Scripting Guy who writes this column and his Scripting Son went to the tournament\u2019s opening day. Because they like watching both games, including the game that doesn\u2019t involve the Huskies, they went over early and ate dinner beforehand: deli sandwiches (with tons<\/i> of meat). Yesterday they went over early and had pulled-pork barbecue sandwiches. Tonight is the third and final day of the tournament, which means the Scripting Guy who writes this column is absolutely swamped trying to figure out what they should have for dinner this time around.<\/p>\n
Anyway if you could get back to us, say when the college basketball season is over, that would really help a lot. Thanks, man! <\/p>\n
Just a moment, MW \u2026.<\/p>\n
It seems that the Scripting Editor \u2013 who is becoming more and more of a killjoy by the moment (as if that was even possible) \u2013 has informed us that we were kidding when we suggested that we weren\u2019t going to answer any more scripting questions until after the college basketball season was over. To tell you the truth, we didn\u2019t think <\/i>we were kidding; we thought we were serious. (Hey, the season will be over sometime in April.) On the other hand, maybe the Scripting Editor is right. After all, the law of averages suggests that sooner or later she\u2019s bound to be right about something<\/i>. Maybe today\u2019s the day.<\/p>\n
Well, just between us, we don\u2019t think she is, either. But what the heck; we\u2019ll humor the Scripting Editor and answer the question anyway:<\/p>\n
arrValues = Array(1,5,7,9,11,13,15,17)\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nobjExcel.Workbooks.Add\ni = 1\nFor Each intValue in arrValues\n objExcel.Cells(i, 1).Value = intValue\n i = i + 1\nNext\nobjExcel.Cells(i, 1).Formula = \"=SUM(A1:A\" & i - 1 & \")\"\n<\/pre>\nBefore we go much further we should point out that we\u2019re working with a very simple, one-column spreadsheet here. That might not be especially realistic, but it should make it easier for you to follow what we\u2019re doing. To make a fancier, multi-column spreadsheet all you need to do is take the basic recipe shown here and extend it a little. <\/p>\n
OK. As you can see, we start out simply by assigning a bunch of numbers to an array named arrValues:<\/p>\n
arrValues = Array(1,5,7,9,11,13,15,17)\n<\/pre>\nWhy do we do this? That\u2019s easy: we need a bunch of values to add to the spreadsheet, and this seemed as good a way as any to do that. Do you need to put all of your values into an array before adding them to your<\/i> spreadsheet? Probably not. We went this route simply because it helps us keep the code as short and sweet and possible.<\/p>\n
After adding values to our array we then use the following block of code to create an instance of the Execl.Application<\/b> object, make that instance visible, and then give that instance a new workbook:<\/p>\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nobjExcel.Workbooks.Add\n<\/pre>\nWith Excel up and running, and visible onscreen, we then set the value of a counter variable named i \u2013 a variable we\u2019ll use to keep track of the current row in the spreadsheet \u2013 to 1:<\/p>\n
i = 1\n<\/pre>\nAs soon as that\u2019s done we\u2019re ready to start adding data to our spreadsheet. To do that we simply set up a For Each loop that loops through each value in the array arrValues. Inside that loop we use this line of code to set the value of the cell in column 1 of the current row to the value we grabbed from the array:<\/p>\n
objExcel.Cells(i, 1).Value = intValue\n<\/pre>\nIf you look closely at the code you can see that we use the counter variable i to represent the row. (Hey, just like we said we were going to do!) The first time through the loop i is equal to 1; as it turns out, the first value we grab from the array is also equal to 1. What does all that mean? That means, the first time through, cell row 1, column 1 (A1) will end up with a 1 in it. We then increment the value of i by 1, meaning that, the second time through the loop, i will be equal to 2. In turn, that means the next value in the array (3) will be plopped into cell row 2, column 1 (A2).<\/p>\n
And you\u2019re right: that part is<\/i> pretty easy, isn\u2019t it? each time we grab a value from the array we simply drop down another row and assign that value to column 1.<\/p>\n
We have 8 values in our array; that means that, by the time we exit the loop, cells A1 through A8 will have values. That also means that we\u2019re ready for the last step in the process: putting a formula into cell A9 that adds all the values in cells A1 through A8. How do we do that? Like this:<\/p>\n
objExcel.Cells(i, 1).Formula = \"=SUM(A1:A\" & i - 1 & \")\"\n<\/pre>\nAll we\u2019re doing here is assigning a value to the Formula<\/b> property for cell A9 (row 9, column 1). Why does the code look so weird? Well, the easiest way to sum several rows of cells is to use a formula similar to this:<\/p>\n=SUM(A1:A8)\n<\/pre>\nThat\u2019s fine, except that it means we need to construct this formula ourselves. In particular, we need to concatenate the following items:<\/p>\n
\n\n\n| \u2022<\/td>\n | \n =SUM(A1:A<\/b>, which simply represents the beginning of the formula.<\/p>\n<\/td>\n<\/tr>\n\n| \u2022<\/td>\n | \n i \u2013 1<\/b>, which represents the current row minus 1<\/i>. Why minus 1? Well, remember, we\u2019re currently in row 9; if we used the variable i we\u2019d end up putting a formula in cell A9 that would then to add up all the values in cells A1 through A9. That\u2019s right: the dreaded circular reference. (Not that anything like that happened the first time we tried this script, of course.) The mini-equation i \u2013 1 is enough to prevent a circular reference.<\/p>\n<\/td>\n<\/tr>\n\n| \u2022<\/td>\n | \n )<\/b>, which simply closes out the formula.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n In other words:<\/p>\n =SUM(A1:A\n+ 8\n+ )\n __________________\n =SUM(A1:A8)\n<\/pre>\nWill that give us the sum total for all the values in cells A1 through A8? Give it a try and see for yourself. And if you\u2019d like to know a little more about working with formulas in Excel (as well as a quick tip for converting a cell address like row 8, column 1 to A8) then take a peek at this Office Space column<\/b><\/a>.<\/p>\nAs for us, it\u2019s back to work: so far we\u2019ve narrowed it down to Italian or Chinese. Although there is<\/i> a diner nearby that has really good pie. We have to tell you the truth: when you\u2019re a Scripting Guy you are under enormous pressure from the time you get up until the time you go back to bed. It\u2019s a tough job, but someone has to do it.<\/p>\n Well, at least until we can get rid of the Scripting Editor. (Editor\u2019s Note: <\/i>The Scripting Guys need to get much more creative in their attempts to get rid of the Scripting Editor if they hope to succeed. Fortunately [for who, we\u2019re not sure] they\u2019re too lazy to put much effort into it. Hopefully they\u2019ll never think to get tips from the Scripting Editor\u2019s last team\u2026.<\/i>)<\/i><\/p>\n","protected":false},"excerpt":{"rendered":" Hey, Scripting Guy! How can I add a bunch of rows to an Excel spreadsheet and then stick a total row at the bottom? — MW Hey, MW. Could we ask you a favor: is there any chance that you could write back later with this question? As you may or may not know, the […]<\/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,711,48,49,3,5],"class_list":["post-66033","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":" Hey, Scripting Guy! How can I add a bunch of rows to an Excel spreadsheet and then stick a total row at the bottom? — MW Hey, MW. Could we ask you a favor: is there any chance that you could write back later with this question? As you may or may not know, the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66033","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=66033"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66033\/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=66033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | | |
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")