|
|
|
|
|
|
|
|
|
|
|
|
|
|
SCRIPTING TUTORIAL
OLE automation primer
By Chris Stoner, a.ka. GreenJellyBean
Ever wanted to manipulate your Notes data with another application, but didn't know where to start? Leveraging the strengths of other applications can be tricky, but the rewards are great. Some applications can provide capabilities that Notes either does not have at all or does not do very well. One example is sharing data with a spreadsheet. This OLE automation primer will get you familiar with launching and manipulating another application from Notes using LotusScript.
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.
| Field name |
Type |
Value |
| Initial_Balance |
Editable, Number, Currency |
1,000 |
| Deposit |
Editable, Number, Currency |
200 |
| Cash_Withdrawn |
Editable, Number, Currency |
130 |
| Monthly_Charges |
Editable, Number, Currency |
20 |
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- Advertisement --
Learn Notes and Domino 8 at your place and pace!
Learn Notes and Domino in your office and/or home! TLCC's highly acclaimed distance learning courses for users, developers, and admins will enhance your career and your resume.
The many included activities and demos will make you a pro! Expert instructor help is a click away.
Click here to try a FREE demo course!! |
-- 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. |
|
|
|
|
|
|
|
|
|
|
