A little background
OLE (Object Linking and Embedding) automation allows one application, called the OLE server, to use another application, called the OLE client, to do some work. In our case, the OLE server will be Notes, and the OLE client will be Excel.

What we hope to do is launch Excel, send it some Notes data, have Excel crunch some numbers, and return a result which we will store in our Notes document. The example is simple, but should serve as a good starting point for you to integrate other applications with Lotus Notes.

Getting started
The Notes form is where we will do all the work in this example. If you look at Table A, you can see that we will have four fields that will store some numbers for us. We'll be sending these numbers to Excel later on.

This form will mimic a checkbook. The Initial_Balance field will represent the Balance in our checkbook before all transactions. Cash_Withdrawn and Monthly_Charges are both subtractions from our Initial_Balance, while Deposit is an addition to our Initial_Balance. Create a button called "Calculate in Excel", but don't add any code to it just yet. We'll add the LotusScript code to this button in the next section.

A new Notes document created with this form should look like what you see in Figure A.

FIGURE A


Here's what your new Notes document should look like. Roll over picture for a larger image.

The script
To get these values out to Excel, we'll need to put some code behind that "Calculate in Excel" button. Copy and paste the script located at http://www.component-net.com/dp-extras/oleauto-0999.html into the Click event.

In depth
While I tried to include detailed remarks in the code, we should step through it. Also, it can be beneficial to turn on the Notes Debugger to see what's happening under the hood.

After getting a handle to the current Notes Document, it's necessary to Dim and Create our Excel objects. The first object is the application itself, which is Excel. Excel must be Dim'd as a variant. oWorkbook will be the handle to our Excel workbook. These two objects drive the entire script, and though in a more complex script, it may be necessary to create other objects, like a Sheet object.