yesterday\u2019s column<\/b><\/a> was actually written by James Joyce, the famous Irish novelist, all part of an ongoing attempt to audition potential new writers for this column. Joyce\u2019s article was \u2026 interesting \u2026 but perhaps a little bit obtuse, even for Hey, Scripting Guy!<\/i>. Because of that, we decided to be a little more conservative with today\u2019s column, eschewing the use of famous novelists altogether. Instead, today\u2019s column is being written by a much more traditional writer, someone familiar to anyone who\u2019s ever visited the Script Center: the Scripting Dog. It\u2019s all yours, Scripting Dog.<\/p>\nWOOF, WOOF, WOOF!<\/p>\n
Oh, sorry; I was barking at the Pekinese across the street. <\/p>\n
Before I begin I would like to thank the Scripting Guys for giving me this opportunity. No doubt many of you have read technical documentation in the past and thought, \u201cMan, my dog<\/i> could write better documentation than that.\u201d And you\u2019re right: your dog could<\/i> write better documentation than that. Sadly, however, dogs have never been given a chance to prove their worth as technical writers, at least not until now. So, thank you again, Scripting Guys. We know that you guys don\u2019t get along with people all that well, but the dogs of the world salute you.<\/p>\n
And the fact that neither one of you owns a cat is just the icing on the cake.<\/p>\n
Speaking of which, I could really go for a piece of moldy old cake right about now. I wonder if there\u2019s a garbage can around here?<\/p>\n
Never mind; I have a job to do. JK needs a script that reads a spreadsheet row-by-row, and extracts specified information from each of those rows. How can we help JK out? Like this:<\/p>\n
Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\n\nintRow = 4\nintColumn = 2\n\nDo Until objWorksheet.Cells(intRow, 1) = \"\"\n strID = \"Employee ID: \" & objWorksheet.Cells(intRow, 1) & vbCrLf\n\n Do Until objWorksheet.Cells(intRow, intColumn) = \"\"\n Select Case objWorksheet.Cells(intRow, intColumn)\n Case \"PL\"\n strPLText = strPLText & objWorksheet.Cells(3, intColumn) & \" -- Paid leave\" & vbCrLf\n intPL = intPL + 1\n Case \"LWP\"\n strLWPText = strLWPText & objWorksheet.Cells(3, intColumn) & \" -- Leave without pay\" & vbCrLf\n intLWP = intLWP + 1\n Case \"SCK\"\n strSCKText = strSCKText & objWorksheet.Cells(3, intColumn) & \" -- Sick leave\" & vbCrLf\n intSCK = intSCK + 1\n Case \"HD\"\n strHDText = strHDText & objWorksheet.Cells(3, intColumn) & \" -- Half day\" & vbCrLf\n intHD = intHD + 1\n Case \"OP\"\n strOPText = strOPText & objWorksheet.Cells(3, intColumn) & \" -- On pool list\" & vbCrLf\n intOP = intOP + 1\n End Select\n intColumn = intColumn + 1\n Loop\n\n If intPL + intLWP + intSCK + intHD + intOP > 0 Then\n Wscript.Echo vbCrLf & \"====================================\" & vbCrLf & strID \n\n If intPL > 0 Then\n Wscript.Echo strPLText \n End If\n\n If intLWP > 0 Then\n Wscript.Echo strLWPText \n End If\n\n If intSCK > 0 Then\n Wscript.Echo strSCKText \n End If\n\n If intHD > 0 Then\n Wscript.Echo strHDText \n End If\n\n If intOP > 0 Then\n Wscript.Echo strOPText \n End If\n End If\n\n If intPL > 0 Then\n Wscript.Echo \"Paid leave: \" & intPL\n End If\n\n If intLWP > 0 Then\n Wscript.Echo \"Leave without pay: \" & intLWP\n End If\n\n If intSCK > 0 Then\n Wscript.Echo \"Sick leave: \" & intSCK\n End If\n\n If intHD > 0 Then\n Wscript.Echo \"Half day: \" & intHD\n End If\n\n If intOP > 0 Then\n Wscript.Echo \"On pool list: \" & intOP\n End If\n\n If intPL + intLWP + intSCK + intHD + intOP > 0 Then\n Wscript.Echo \"Total leave days: \" & intPL + intLWP + intSCK + intHD + intOP\n End If\n\n intPL = 0\n intLWP = 0\n intSCK = 0\n intHD = 0\n intOP = 0\n\n strPLText = \"\"\n strLWPText = \"\"\n strSCKText = \"\"\n strHDText = \"\"\n strOPText = \"\"\n\n intColumn = 2\n intRow = intRow + 1\nLoop\n<\/pre>\nTwo things to note before we explain how this script works. To begin with, the script is a little longer than it needs to be. (Well, OK, it\u2019s quite a bit<\/i> longer than it needs to be.) That\u2019s because we decided to get a little fancy here: not only did we group all the leave days under the appropriate category (e.g., Leave without pay), but we also provided a nice little summary (showing total number of sick days, total number of paid leave days, etc.) for each employee. If all we needed to do was show the dates that each employee was absent, and the reason for that absence, we could reduce the size of the script by at least half. But we didn\u2019t want to do a half-hearted job here. After all, that\u2019s something a cat<\/i> would do.<\/p>\n
Second, I would not be fulfilling my Scripting Dog oath if I did not mention that this task could probably be performed a lot easier if the data was in a database rather than a Microsoft Excel spreadsheet. But that\u2019s just a suggestion and it\u2019s not a big deal; as you\u2019re about to see, we\u2019ll be able to work with the spreadsheet without any problem.<\/p>\n
Excuse me a moment; I think I saw a squirrel outside \u2013 WOOF! WOOF! WOOF, WOOF, WOOF!<\/p>\n
Stupid squirrel.<\/p>\n
Now, where was I? Oh, right. Speaking of Excel spreadsheets, JK\u2019s looks something like this, with each row representing the daily attendance for a given employee:<\/p>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/readrows.jpg\")
<\/p>\n
How are we going to determine the various leave types used by each employee, along with the dates he or she was off work? Well, before we can even think<\/i> about doing that we need to create an instance of the Excel.Application<\/b> object and then set the Visible<\/b> property to True; that\u2019s going to give us a running instance of Microsoft Excel that we can see onscreen. We\u2019re then going to use these two lines of code to open the file C:\\Scripts\\Test.xls, then bind to the first worksheet in that file:<\/p>\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nSet objWorksheet = objWorkbook.Worksheets(1)\n<\/pre>\nOur next step is to assign values to a pair of variables:<\/p>\n
intRow = 4intColumn = 2<\/pre>\nAs the name implies, we\u2019ll use intRow to keep track of our current row in the spreadsheet. Why do we set intRow to 4? That\u2019s easy; row 4 is the first row where we actually have employee data. (Rows 1 through 3 are header rows.) Likewise, we\u2019ll use intColumn to keep track of the current column; we set that to 2 because column 2 is the first column<\/i> where we find actual data. (Column 1 is the employee ID number.)<\/p>\n
That brings us to this line of code:<\/p>\n
Do Until objWorksheet.Cells(intRow, 1) = \"\"<\/pre>\nWhat we\u2019re doing here is setting up a Do Until loop that runs until we encounter a cell in column 1 that\u2019s empty; if we find an empty cell in column 1 that means we\u2019ve reached the end of the worksheet. Inside this loop the first thing we do is grab the value of column 1 (for the current row, of course) and append that to a variable named strID:<\/p>\n
strID = \"Employee ID: \" & objWorksheet.Cells(intRow, 1) & vbCrLf<\/pre>\nIn other words, strID will now have a value similar to this (depending, of course, on the employee ID number):<\/p>\n
Employee ID: 25192<\/pre>\nThe second thing we do inside this loop is set up yet another Do Until loop:<\/p>\n
Do Until objWorksheet.Cells(intRow, intColumn) = \"\"<\/pre>\nWhat are we doing with this<\/i> loop? Well, the first time through our initial loop we\u2019ll be working in row 4, and reading across the spreadsheet to grab leave information for this first employee. How many columns of information will we have to read before we\u2019ve grabbed all that information? To be honest, we have no idea. But that\u2019s fine; thanks to this loop, we\u2019ll just keep reading each column, one-by-one, until we encounter a blank cell; as soon as that happens, then we\u2019ll know that we\u2019ve run out of data for this employee.<\/p>\n
Wow, that\u2019s really weird: it looks like some kind of a tail sticking out behind me; where does that keep coming from? Let me see if I can grab it. Shoot; just missed it. Let me try again. Dang. OK, one more time. Oh, I almost had it that time! Let me try again \u2026.<\/p>\n
Sorry; I spent 15 minutes trying to grab that tail and never could<\/i> get it. Oh, well, back to the script. Inside the interior loop we set up a Select Case statement that examines the value of the current cell (the first time through the loop, that will be cell row 4, column 2). This Select Case statement is designed to identify the various types of leave an employee might have used. For example, take a look at this block of code:<\/p>\nCase \"PL\"\n strPLText = strPLText & objWorksheet.Cells(3, intColumn) & \" -- Paid leave\" & vbCrLf\n intPL = intPL + 1\n<\/pre>\nSuppose an employee took paid leave one day. In that case, the cell for that particular day will be marked PL<\/b>. In this block of code we\u2019re checking to see if the value of the cell is<\/i> PL. If it is, then we\u2019re going to do two things. First, we\u2019re going to add the date of the paid leave to a variable named strPLText. How do we know the date that the leave was taken? That\u2019s easy; the date can be found in row 3 of the current column. Hence our use of the syntax objWorksheet.Cells(3, intColumn)<\/b>. <\/p>\n
Second, we increment the counter variable intPL by 1. Why? Well, we\u2019re using this counter variable to keep track of the number of days of paid leave taken by this first employee. We\u2019ve just found a day of paid leave taken by the employee, so intPL (which starts off life being equal to 0) needs to be incremented by 1.<\/p>\n
We then perform similar checks \u2013 and take similar actions \u2013 for each of the other leave types. What if the employee simply worked that day (interesting concept, eh?) and didn\u2019t actually take leave? That\u2019s fine; in that case we don\u2019t do anything at all.<\/p>\n
After we\u2019ve gathered all the data for employee 1 we then execute this line of code:<\/p>\n
If intPL + intLWP + intSCK + intHD + intOP > 0 Then<\/pre>\nWhat we\u2019re doing here is adding up the values of all our counter variables, including intPL (the variable that tracks paid leave days), intLWP (the variable that tracks leave without pay days), and so on. If the sum of all our counter variables is greater than 0 that means that this employee has taken at least one leave day. Because of that, we then use this line of code to echo the employee ID to the screen:<\/p>\n
Wscript.Echo vbCrLf & \"====================================\" & vbCrLf & strID<\/pre>\nAs you can see \u2013 WOOF, WOOF, WOOF!<\/p>\n
Sorry. I thought I heard a doorbell, but it must have been on TV. Anyway, as you can see, we actually echo more than just the employee ID; we also echo a few equal signs to make it easier to determine where one employee\u2019s data ends and the next employee\u2019s data begins. On screen, that header looks like :<\/p>\n
====================================\nEmployee ID: 25192\n<\/pre>\nWell, we never said it was a cool<\/i> header, just that it was a header.<\/p>\n
In turn, that brings us to a series of code blocks similar to this one:<\/p>\n
If intPL > 0 Then Wscript.Echo strPLText End If<\/pre>\nIn this case, we\u2019re simply checking to see if the employee took any paid leave days; if he did (or if she did), then the counter variable intPL will be greater than 0. If that proves to be true, then we simply echo back the date of each paid leave, a value we stored in the variable strPLText. We then repeat this process for the other leave types. For example, this block of code reports back sick leave days:<\/p>\n
If intSCK > 0 Then Wscript.Echo strSCKText End If<\/pre>\nOnce we\u2019ve disposed of all the leave types we echo back our summary information; for example, this block of code reports back the total leave without pay days taken by the employee:<\/p>\n
If intLWP > 0 Then Wscript.Echo \"Leave without pay: \" & intLWPEnd If<\/pre>\nFinally, we echo back the total number of leave days taken by the employee:<\/p>\n
If intLWP > 0 Then\n Wscript.Echo \"Leave without pay: \" & intLWP\nEnd If\n<\/pre>\nFinally, that we reset all our counter variables back to 0, then set our leave variables (e.g., strPLText) back to empty strings. We increment our row by 1 (simply by adding 1 to the variable intRow), set the value of intColumn to 2 (so that we\u2019ll start over again with the data in column 2), and then go back to the top of our first loop and repeat this entire process with the next row in the spreadsheet. When all is said and done, we should have an onscreen report that looks something like this (well, for one employee anyway):<\/p>\n
Employee ID: 25314\n\n4\/16\/2008 -- Paid leave\n4\/17\/2008 -- Paid leave\n4\/18\/2008 -- Paid leave\n4\/21\/2008 -- Paid leave\n4\/22\/2008 -- Paid leave\n4\/23\/2008 -- Paid leave\n4\/24\/2008 -- Paid leave\n4\/25\/2008 -- Paid leave\n4\/29\/2008 -- Paid leave\n4\/30\/2008 -- Paid leave\n\n4\/28\/2008 -- Sick leave\n5\/1\/2008 -- Sick leave\n\nPaid leave: 10\nSick leave: 2\nTotal leave days: 12\n<\/pre>\nThat\u2019s all we have \u2026.<\/p>\n
Sorry; I seem to have dozed off for a few minutes. (That seems to happen every now and then.) Anyway, like I started to say, that\u2019s all we have to do. Give that a try, JK, and let us know if you run into any problems.<\/p>\n
Thank you, Scripting Dog; here\u2019s a piece of cheese. Which, interestingly enough, is the very same reward given to the Scripting Guy who typically writes this column after he<\/i> finishes writing this column. Hmmm, maybe he can be replaced easier than we thought \u2026.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have an Excel spreadsheet that tracks daily time for our employees, indicating whether the employee worked on a given day or took some type of leave (paid leave, leave without pay, sick leave, etc.). Is there a way to output a report showing the various leave types taken by each employee, […]<\/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,5],"class_list":["post-55233","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-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have an Excel spreadsheet that tracks daily time for our employees, indicating whether the employee worked on a given day or took some type of leave (paid leave, leave without pay, sick leave, etc.). Is there a way to output a report showing the various leave types taken by each employee, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55233","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=55233"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55233\/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=55233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55233"}],"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,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\n