Have you wanted to create Excel spreadsheets from within Notes but did not know how to do it? Standard Notes views are pretty good, but maybe they do not solve all your problems. You can always export a view and then bring it in to Excel, But what about create 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 .
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 ....................
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 openDone: msExcel.Visible = False If fileName = "" Then Call msExcel.Workbooks.Add Else Call msExcel.Workbooks.Open (fileName) End If Set OpenMSExcel = msExcel Exit FunctionCreateNewInstance: 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 Returnedend 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, This is actually pretty Easy. You Should Have a variable called
Worksheet That Is Defined As a Variant, And Another ONE CALLED
Cell this is also a variant. (The name'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 Object (The one returned by the
Openmsexcel function) and finds the first worksheet. It dams 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 '- All You Have to do is record a macro triaes what it wants and then use what code. But Again, I'm going to make you wait. Let's Go Back to oor basketball example. You have 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........................ ..
'Go Through all the entries in the view while not entry is nothing set cell = Worksheet.Range ("a" & cstr (rower)) 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) WendNext, 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 area Number values). But Column D and e Are Computed. Take a Look at Those Values - How Did i com Up with what and what do the mean?
For Column "D", The Formula Tells Excel To Take The Data from 2 Column to Take The Data from 2 Column To The LEFT. SO, For ROW 2 ( In column d), The Formula Would Be
B2 / (b2 c2). Where Did That came from?
Well, All I Did Was Record A Macro. In Excel, Under The Tools Menu, There I Chose "Record A New Macro". Name It Whose for You Want - You Won't Be Used It for Very Long. do what you want to do in the macro and the copy, i went to a row in column d, hit the key to start a formula, mo Move 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 buy 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 Beenrated. ", 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 ., Singapore, singapore
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 are not 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 what value in notes (or set your own constant).
