Dim oExcel As Variant Dim oWorkbook As Variant 'create excel object, which launches the Excel application Set oexcel = CreateObject("Excel.Application")

Setting Visible = True (see below) allows Excel to be visible once invoked. To hide Excel, either remove this line of code, or set oexcel.Visible = False.

'set to visible so we can see what we're doing... oexcel.Visible = True

Creating any new handles, such as our handle, in the Workbook using the Variant oworkbook must all derive from our Application Object, oexcel. This is important to remember, especially if another object, such as a Sheet, needs to be created.

The .Add method, shown below, actually creates a new file in our Excel workspace that we can write to.

'handle to Workbook Set oworkbook = oexcel.Workbooks oworkbook.Add

Now it's time to write to our cells in this new workbook. The method for writing to a cell in a table is to use .cells(0,0), where the first number is the row and the second number is the column that we want to write to.

'write to first cell, first row oexcel.cells(1,1) = doc.Initial_Balance(0) oexcel.cells(2,1) = doc.Deposit(0)

In this example, Cash_Withdrawn and Monthly_Charges are deductions from our account, so it is necessary to prefix these values with a minus sign. This way, Excel knows we want to subtract these amounts.

oexcel.cells(3,1) = "-" & doc.Cash_Withdrawn(0) 'added a minus to denote a charge against our account oexcel.cells(4,1) = "-" & doc.Monthly_Charges(0) 'here too

Next, Excel needs to know which cells we want to manipulate. Use the Range property and Select to highlight all four rows of data we just entered.

'select all 4 rows oexcel.Range("A1:A4").Select

These amounts are Currency, so use Selection.Style to set the cell data type to Currency. If you are stepping through the code, you will see Dollar Signs appear.

'set the style to Currency oexcel.Selection.Style = "Currency"

Now we must Select all four rows, plus an extra row, so Excel will have a place to put the Total when we add up, or sum, these rows.

'select the 4 rows, plus an extra row (for our total) oexcel.Range("A1:A5").Select

This next bit of code will actually do the Sum and put the total in cell A5.

'Add up the 4 rows with the total in the 5th row oexcel.Range("A5").Activate oexcel.ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

This formula may look confusing, but fear not! There's a way to get to the VBA IDE for operations that are not familiar to us Notes Developers. With Excel open, click on ALT-F11 and the VBA Development Environment will open. There's a nice object browser in there, and a full Help System that will open up a host of new possibilities with Excel, and other Office Applications. Keep in mind that VBA, a scripting language, has been deployed across all Office Applications, so similar code will work in Word, Powerpoint, etc.