|
|
|
|
|
|
|
|
|
|
|
|
|
|
OLE automation primer (continued)
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- Advertisement --
Sophisticated Meets Simple For Document Management
Share. Control. Manage.
Documents, emails, and content in the context of how work is done.
Native to Lotus Domino. The User Experience unseen for Lotus Domino.
Do more with less. Really.
See the possibilities Docova unleashes for Lotus Domino. |
-- Advertisement --
Mark your calendar for in-depth Lotus training, May 12-14, Boston
Join experts and peers May 12-14 in Boston for educational and networking events that deliver real-world Lotus training so you can increase productivity and efficiency in your company, advance your skills, and squeeze the most from your current environment. One registration gets you into THE VIEW's Admin2010 and Lotus Developer2010.
Register by April 10 to save $200. |
|
|
|
|
|
|
|
|
|
|
