Now, since we have used lists to gather the data, writing the data out to the Excel spreadsheet doesn't contain a single loop!! How cool is that?!? First, let me explain what some of the other subroutines that you have seen do:

• SetExcelOptions - This is where I handle the page setup. I set the default font, page header and footer, and orientation.
• SetWorksheetTitleRows - This is where the first row and column entries are set and where the format for the data columns are set. For most reports, I only set the top row, but since this report is a little more structured, I am also setting the first column on the first and third pages. The code in here sets not only the values for the cells, but also sets the look and feel for those cells. Here is some of that code:
  
  Dim thisselection As Variant
    
    With xlSheet
      .Name = "Overall Statistics"
      
      ' Top Header Row
      Set thisselection = .Range(.Cells(1,1),.Cells(1,8))
      With thisselection
        .NumberFormat = getNumberFormat("Text")
        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlLeft
        .Font.Bold = True
        .Font.Italic = False
        .Font.Underline = False
        .WrapText = True
        .Font.ColorIndex = 1
        .Orientation = 0
        .MergeCells = False
        .Interior.ColorIndex = 15
        .Interior.Pattern = xlSolid
        With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .ColorIndex = xlAutomatic
        End With
      End With
      
      .cells(1,1).Value = ""
      .cells(1,2).Value = "Full Day Office Interview"
      .cells(1,3).Value = "Pass"
      .cells(1,4).Value = "Offer Made"
      .cells(1,5).Value = "Rejected Offer"
      .cells(1,6).Value = "Accepted Offer"
      .cells(1,7).Value = "Hit Ratio: Accepted vs Interviewed"
      .cells(1,8).Value = "Hit Ration: Accepted vs Offered"
  
  All of the "xl" values are constants that I set in the declarations section. By using the .Range to get a selection of cells, I am able to set the attributes of all those cells without looping through each cell. BTW, most of the Excel specific code was created by using the Record Macro function in Excel to create VBA that I wanted to mimic in LotusScript. Since the 3 sheets in this workbook as very different, I have 3 versions of this sub in the agent.
• getNumberFormat - This gets the codes that set the correct number format for the cells. The code for text is "@" while the code for showing only Month and Year of a date is "mmm yyyy".

Once we have the sheets setup like we want them, we use the AddStatstoReport sub to populate the sheet. Here is a portion of that sub:

Sub AddStatstoReport
  Call AddLine(3,"Total")
  Call AddPercentLine(4)
  Call AddLine(6,"Company 1")
  Call AddLine(7,"Company 2")
  Call AddLine(8,"Company 3")
  Call AddLine(10,"Mid-Atlantic")
  Call AddLine(11,"Midwest")
  Call AddLine(12,"Northest")
  Call AddLine(13,"Northern California")
  Call AddLine(14,"Southeast")
  Call AddLine(15,"Southwest")
  Call AddLine(16,"West")

Each row is populated by calling the AddLine sub and passing the row number and the identifier. For the second sheet of this report, calling this Sub is done using a ForAll loop and a counter to calculate identifier and row number. Here is what the AddLine sub looks like:

Sub AddLine(row As Integer, listname As String)
  If Iselement(statint(listname)) Then xlSheet.cells(row,2).Value = statint(listname) Else xlSheet.cells(row,2).Value = 0
  If Iselement(statpass(listname)) Then xlSheet.cells(row,3).Value = statpass(listname) Else xlSheet.cells(row,3).Value = 0
  If Iselement(statoffer(listname)) Then xlSheet.cells(row,4).Value = statoffer(listname) Else xlSheet.cells(row,4).Value = 0
  If Iselement(statreject(listname)) Then xlSheet.cells(row,5).Value = statreject(listname) Else xlSheet.cells(row,5).Value = 0
  If Iselement(stataccept(listname)) Then xlSheet.cells(row,6).Value = stataccept(listname) Else xlSheet.cells(row,6).Value = 0
  xlSheet.cells(row,7).FormulaR1C1 = "=if(R" & row & "C2 <> 0,R" & row & "C6/R" & row & "C2,0)"
  xlSheet.cells(row,7).NumberFormat = getNumberFormat("Percent")
  xlSheet.cells(row,8).FormulaR1C1= "=if(R" & row & "C4,R" & row & "C6/R" & row & "C4,0)"
  xlSheet.cells(row,8).NumberFormat = getNumberFormat("Percent")
End Sub

Again, the IsElement function is used to see if the identified exists for each List and, if it does, the value for that identified is retrieved. Since a couple of the cells contain formulas that evaluate out to percentages, we also set the NumberFormat for those cells here. If you have never seen the FormulaR1C1 syntax before, I would suggest getting to know it since it is the easiest way to write Excel formulas from LotusScript.

So there you have it. With a little bit of ingenuity and some efficient coding, you can use Lists and Excel to create some really nice reports that anyone in your company can run. I have posted the code for the Agent in it's entirety for you to dissect at your leisure. Just be nice in your comment as I bruise easily.