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).
