Have you wanted to create Excel spreadsheets from within Notes but didn't know how to do it? Standard Notes views are pretty good, but maybe they don't solve all your problems. You can always export a view and then bring it in to Excel, but what about creating it automatically?

To provide an example, I'll do something in honor of the NCAA Basketball Final Four being played this upcoming weekend. Let's say you're tracking results of games and you have a view that shows how each conference is at defending their home court. So your view is categorized by conference name. The second column is a 1 if the home team won the game or a 0 if they lost. It shows totals, so the category can show the total number of home wins. The third column is a 0 if the home team won the game or a 1 if they lost. It also shows totals.

You want to show the winning percentage for each team, which you could do in another column of the Notes view, but let's say you want to have a fifth column that shows a running total of every conference so far (percentage total from the 1st row through the current row). You can't do that in a Notes view.

There's only a few things you need to know to be able to create an agent that will generate this spreadsheet for you. The first is that you need to know how to launch Excel using OLE automation. The second thing you need to know is how to navigate around. The third thing you need to know is how to look at Macros in Excel. Why? It's the third thing - I'll cover the first two first.

I wrote a generic function to launch Excel and return a handle to the OLE object. The function takes in an empty string if you want to open a blank spreadsheet, or a file name (fully qualified) if you want to open a specific file:

Function OpenMSExcel(fileName As String) As Variant
   Dim msExcel As Variant
   Dim doc As Variant
   
   On Error Goto CreateNewInstance
   Set msExcel = GetObject("Excel.Application") ' Attempt to grab MS Excel if already open
Done:
   msExcel.Visible = False
   If fileName = "" Then
      Call msExcel.Workbooks.Add
   Else
      Call msExcel.Workbooks.Open(fileName)
   End If
   Set OpenMSExcel = msExcel
   Exit Function
CreateNewInstance:
   Print "Loading Microsoft Excel.... Please Wait...."
   Err = 0 ' Clear the error handler
   Set msExcel = CreateObject("Excel.Application") ' Launch MS Excel if not already open
   Print " "
   Resume Done ' Jump back up to the point where a document will be opened and returned
End Function

This handles the first part - launching Excel. It attempts to get the existing instance. If that errors out, then launch a new instance. Take the application and either create a blank worksheet or open up the file name that was passed in.

The second thing you need to know is how to navigate around. This is actually pretty easy. You should have a variable called worksheet that is defined as a Variant, and another one called cell that is also a Variant. (The names don't matter, but that's what I use). Then, here is some simple code to put some text in the upper left cell:

Set worksheet = excel.Application.Workbooks(1).Sheets(1)
Set cell = worksheet.Range("A1")
cell.FormulaR1C1 = "Some Text"

The first line gets a handle to the object (the one returned by the OpenMSExcel function) and finds the first worksheet. It then gets a handle to the upper left cell ("A1"). It then sticks some text into the cell.

After you master #3 above, you'll be able to do anything. That's because we don't need to tell you everything here - all you have to do is record a macro that does what it wants and then use that code. But, again, I'm going to make you wait. Let's go back to our basketball example. You have a view called vwByConference that has all your categorized data. So let's build an agent that will create our Excel spreadsheet for us:

Sub Initialize
   Dim session As New NotesSession
   Dim db As NotesDatabase
   Dim excel As Variant
   Dim worksheet As Variant
   Dim cell As Variant
   Dim view As NotesView
   Dim nav As NotesViewNavigator
   Dim entry As NotesViewEntry
   Dim rowNum As Integer
   
   Set excel = OpenMSExcel("")

First, we define some variables we will need and then open up a blank Excel spreadsheet.

   ' Print out the header row
   Set worksheet = excel.Application.Workbooks(1).Sheets(1)
   Set cell = worksheet.Range("A1")
   cell.FormulaR1C1 = "Conference"
   Set cell = worksheet.Range("B1")
   cell.FormulaR1C1 = "Home Wins"
   Set cell = worksheet.Range("C1")
   cell.FormulaR1C1 = "Home Losses"
   Set cell = worksheet.Range("D1")
   cell.FormulaR1C1 = "Home Win %"
   Set cell = worksheet.Range("E1")
   cell.FormulaR1C1 = "Cumulative Win %"

Next, we print out the header row of information.

   rowNum = 2 ' Current row of data
   
   ' Get a NotesViewNavigator from our view
   Set db = session.CurrentDatabase
   Set view = db.GetView("vwByConference")
   Set nav = view.CreateViewNav
   Set entry = nav.GetFirst

Next, we set a variable that will point to the current row. There are other ways to do this (you can actually do navigation - moving down, etc) in Excel, but I like this approach because I can always tell where I'm at and I could do things like print out an "error" message if the number of rows is getting pretty big. Then we set up the NotesViewNavigator object to point to the first category in our view.

   ' Go through all the entries in the view
   While Not entry Is Nothing
      Set cell = worksheet.Range("A" & Cstr(rowNum))
      cell.FormulaR1C1 = entry.ColumnValues(0)
      Set cell = worksheet.Range("B" & Cstr(rowNum))
      cell.FormulaR1C1 = entry.ColumnValues(1)
      Set cell = worksheet.Range("C" & Cstr(rowNum))
      cell.FormulaR1C1 = entry.ColumnValues(2)
      Set cell = worksheet.Range("D" & Cstr(rowNum))
      cell.FormulaR1C1 = "=+RC[-2]/(RC[-2]+RC[-1])"
      Set cell = worksheet.Range("E" & Cstr(rowNum))
      cell.FormulaR1C1 = "=SUM(RC[-3]:R2C[-3])/(SUM(RC[-3]:R2C[-3])+SUM(RC[-2]:R2C[-2]))"
      rowNum = rowNum + 1
      Set entry = nav.GetNextCategory(entry)
   Wend

Next, we navigate through all the categories in the view. For each category, the values for the first three columns come from the view (the first is a string value and the next two are number values). But column D and E are computed. Take a look at those values - how did I come up with that and what do they mean?
For column "D", the formula tells Excel to take the data from 2 columns to the left, divided by the sum of the data from 2 columns to the left and the data from 1 column to the left. So, for row 2 (in column D), the formula would be B2/(B2+C2). Where did that come from?

Well, all I did was record a macro. In Excel, under the Tools menu, there is a Macro menu, and then I chose "Record A New Macro". Name it whatever you want - you won't be using it for very long. Do what you want to do in the macro and then click the Stop button. In my case, I went to a row in column D, hit the + key to start a formula, moved over to the B column, pressed the / key, etc. Then, once the macro was recorded, I went back to Tools | Macros | Macros. A dialog box appeared. I selected my macro and clicked on Edit. A debugger appeared that shows my code. That's what I copied and used in my formula.

What about column "E"? That's a little more complicated, but again I used a macro. I wanted the sum to be fixed from row #2, but relative down to the current row. So "R2C-3" tells Excel to use row #2 through a relative column (3 to the left of the current position).

Now, back to the code. Let's do some cleanup and formatting at the end:

   ' Do some formatting
   worksheet.Rows("1:1").RowHeight = 25.5
   Set cell = worksheet.Range("A1:E1")
   cell.WrapText = True
   cell.Font.FontStyle = "Bold"
   worksheet.Columns("A:A").ColumnWidth = 7.43
   worksheet.Columns("B:B").ColumnWidth = 8
   worksheet.Columns("C:C").ColumnWidth = 9.43
   worksheet.Columns("D:D").ColumnWidth = 8.43
   worksheet.Columns("E:E").ColumnWidth = 10.71
   Set cell = worksheet.Columns("D:E")
   cell.NumberFormat = "0.0%"
   
   excel.Visible = True
   Msgbox "Your Excel spreadsheet has been generated.", 64, "Success"
End Sub

The first row height should be 25.5 pixels, which amounts to two rows high. The text in those cells should wrap (because we're going to decrease the column width) and should be bold. Then I set the column widths. Finally, I set all the format of the data in columns D and E to be percentage with 1 decimal. Again, I used macros to help me out.

Well, that should do it. That agent should build the Excel spreadsheet and format it the way you want. The spreadsheet does the computation, so your agent doesn't need to do any calculations - it simply reads data from Notes and tells Excel to do the computations.

A final word about macros. Sometimes Excel will generate constants for values. For example, when you set text properties it sets everything and uses constants for horizontal alignment and vertical alignment. Obviously, those constants aren't there in Notes. If you want to get the value, then instead of editing the macro (like above), choose the "Step Into" option. The debugger will start and you can mouse over one of the constants and the debugger will pop up a box saying what the actual value of the constant is. So you can use that value in Notes (or set your own constant).

Next week, I'll talk about generating a report from a browser interface.