Hey, Scripting Guy! How Can I Summarize Information Found in a Spreadsheet Row?
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, along with the dates those leaves were taken?
— JK
Hey, JK. As some of you may recall, yesterday’s column 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’s article was … interesting … but perhaps a little bit obtuse, even for Hey, Scripting Guy!. Because of that, we decided to be a little more conservative with today’s column, eschewing the use of famous novelists altogether. Instead, today’s column is being written by a much more traditional writer, someone familiar to anyone who’s ever visited the Script Center: the Scripting Dog. It’s all yours, Scripting Dog.
WOOF, WOOF, WOOF!
Oh, sorry; I was barking at the Pekinese across the street.
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, “Man, my dog could write better documentation than that.” And you’re right: your dog could 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’t get along with people all that well, but the dogs of the world salute you.
And the fact that neither one of you owns a cat is just the icing on the cake.
Speaking of which, I could really go for a piece of moldy old cake right about now. I wonder if there’s a garbage can around here?
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:
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
intRow = 4
intColumn = 2
Do Until objWorksheet.Cells(intRow, 1) = ""
strID = "Employee ID: " & objWorksheet.Cells(intRow, 1) & vbCrLf
Do Until objWorksheet.Cells(intRow, intColumn) = ""
Select Case objWorksheet.Cells(intRow, intColumn)
Case "PL"
strPLText = strPLText & objWorksheet.Cells(3, intColumn) & " -- Paid leave" & vbCrLf
intPL = intPL + 1
Case "LWP"
strLWPText = strLWPText & objWorksheet.Cells(3, intColumn) & " -- Leave without pay" & vbCrLf
intLWP = intLWP + 1
Case "SCK"
strSCKText = strSCKText & objWorksheet.Cells(3, intColumn) & " -- Sick leave" & vbCrLf
intSCK = intSCK + 1
Case "HD"
strHDText = strHDText & objWorksheet.Cells(3, intColumn) & " -- Half day" & vbCrLf
intHD = intHD + 1
Case "OP"
strOPText = strOPText & objWorksheet.Cells(3, intColumn) & " -- On pool list" & vbCrLf
intOP = intOP + 1
End Select
intColumn = intColumn + 1
Loop
If intPL + intLWP + intSCK + intHD + intOP > 0 Then
Wscript.Echo vbCrLf & "====================================" & vbCrLf & strID
If intPL > 0 Then
Wscript.Echo strPLText
End If
If intLWP > 0 Then
Wscript.Echo strLWPText
End If
If intSCK > 0 Then
Wscript.Echo strSCKText
End If
If intHD > 0 Then
Wscript.Echo strHDText
End If
If intOP > 0 Then
Wscript.Echo strOPText
End If
End If
If intPL > 0 Then
Wscript.Echo "Paid leave: " & intPL
End If
If intLWP > 0 Then
Wscript.Echo "Leave without pay: " & intLWP
End If
If intSCK > 0 Then
Wscript.Echo "Sick leave: " & intSCK
End If
If intHD > 0 Then
Wscript.Echo "Half day: " & intHD
End If
If intOP > 0 Then
Wscript.Echo "On pool list: " & intOP
End If
If intPL + intLWP + intSCK + intHD + intOP > 0 Then
Wscript.Echo "Total leave days: " & intPL + intLWP + intSCK + intHD + intOP
End If
intPL = 0
intLWP = 0
intSCK = 0
intHD = 0
intOP = 0
strPLText = ""
strLWPText = ""
strSCKText = ""
strHDText = ""
strOPText = ""
intColumn = 2
intRow = intRow + 1
Loop
Two 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’s quite a bit longer than it needs to be.) That’s 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’t want to do a half-hearted job here. After all, that’s something a cat would do.
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’s just a suggestion and it’s not a big deal; as you’re about to see, we’ll be able to work with the spreadsheet without any problem.
Excuse me a moment; I think I saw a squirrel outside – WOOF! WOOF! WOOF, WOOF, WOOF!
Stupid squirrel.
Now, where was I? Oh, right. Speaking of Excel spreadsheets, JK’s looks something like this, with each row representing the daily attendance for a given employee:
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 about doing that we need to create an instance of the Excel.Application object and then set the Visible property to True; that’s going to give us a running instance of Microsoft Excel that we can see onscreen. We’re 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:
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
Our next step is to assign values to a pair of variables:
intRow = 4intColumn = 2
As the name implies, we’ll use intRow to keep track of our current row in the spreadsheet. Why do we set intRow to 4? That’s easy; row 4 is the first row where we actually have employee data. (Rows 1 through 3 are header rows.) Likewise, we’ll use intColumn to keep track of the current column; we set that to 2 because column 2 is the first column where we find actual data. (Column 1 is the employee ID number.)
That brings us to this line of code:
Do Until objWorksheet.Cells(intRow, 1) = ""
What we’re doing here is setting up a Do Until loop that runs until we encounter a cell in column 1 that’s empty; if we find an empty cell in column 1 that means we’ve 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:
strID = "Employee ID: " & objWorksheet.Cells(intRow, 1) & vbCrLf
In other words, strID will now have a value similar to this (depending, of course, on the employee ID number):
Employee ID: 25192
The second thing we do inside this loop is set up yet another Do Until loop:
Do Until objWorksheet.Cells(intRow, intColumn) = ""
What are we doing with this loop? Well, the first time through our initial loop we’ll 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’ve grabbed all that information? To be honest, we have no idea. But that’s fine; thanks to this loop, we’ll just keep reading each column, one-by-one, until we encounter a blank cell; as soon as that happens, then we’ll know that we’ve run out of data for this employee.
Wow, that’s 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 ….
Sorry; I spent 15 minutes trying to grab that tail and never could 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:
Case "PL"
strPLText = strPLText & objWorksheet.Cells(3, intColumn) & " -- Paid leave" & vbCrLf
intPL = intPL + 1
Suppose an employee took paid leave one day. In that case, the cell for that particular day will be marked PL. In this block of code we’re checking to see if the value of the cell is PL. If it is, then we’re going to do two things. First, we’re 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’s easy; the date can be found in row 3 of the current column. Hence our use of the syntax objWorksheet.Cells(3, intColumn).
Second, we increment the counter variable intPL by 1. Why? Well, we’re using this counter variable to keep track of the number of days of paid leave taken by this first employee. We’ve 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.
We then perform similar checks – and take similar actions – for each of the other leave types. What if the employee simply worked that day (interesting concept, eh?) and didn’t actually take leave? That’s fine; in that case we don’t do anything at all.
After we’ve gathered all the data for employee 1 we then execute this line of code:
If intPL + intLWP + intSCK + intHD + intOP > 0 Then
What we’re 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:
Wscript.Echo vbCrLf & "====================================" & vbCrLf & strID
As you can see – WOOF, WOOF, WOOF!
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’s data ends and the next employee’s data begins. On screen, that header looks like :
==================================== Employee ID: 25192
Well, we never said it was a cool header, just that it was a header.
In turn, that brings us to a series of code blocks similar to this one:
If intPL > 0 Then Wscript.Echo strPLText End If
In this case, we’re 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:
If intSCK > 0 Then Wscript.Echo strSCKText End If
Once we’ve 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:
If intLWP > 0 Then Wscript.Echo "Leave without pay: " & intLWPEnd If
Finally, we echo back the total number of leave days taken by the employee:
If intLWP > 0 Then
Wscript.Echo "Leave without pay: " & intLWP
End If
Finally, 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’ll 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):
Employee ID: 25314 4/16/2008 -- Paid leave 4/17/2008 -- Paid leave 4/18/2008 -- Paid leave 4/21/2008 -- Paid leave 4/22/2008 -- Paid leave 4/23/2008 -- Paid leave 4/24/2008 -- Paid leave 4/25/2008 -- Paid leave 4/29/2008 -- Paid leave 4/30/2008 -- Paid leave 4/28/2008 -- Sick leave 5/1/2008 -- Sick leave Paid leave: 10 Sick leave: 2 Total leave days: 12
That’s all we have ….
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’s all we have to do. Give that a try, JK, and let us know if you run into any problems.
Thank you, Scripting Dog; here’s a piece of cheese. Which, interestingly enough, is the very same reward given to the Scripting Guy who typically writes this column after he finishes writing this column. Hmmm, maybe he can be replaced easier than we thought ….
Light
Dark
0 comments