This applies only to Windows platform. Ensure that MS Excel is installed in your PC. This tutorial is based on the following software environment.

Windows XP Professional, MS Excel 2003, Lotus Notes Client 6, Domino Server 6

Create a new database in server Go to File–>Database–>New. Give the file name as test.nsf

Open the database in Domino designer. Create new form. In form properties set the Name as Employee

Create a table with two rows and two columns. In table properties set the width of cell as 2.0

Now enter text in left cells. Type Name in top left cell and Age in bottom left cell.

Create two fields. First create a text field in top right cell. Name it as EmployeeName.Select type as Text in field properties.

Create Number field in bottom right cell. Name it as EmployeeAge. Select type as Number.

Create a new Action. Name it as Save&Close. Write the formula as

@Command([FileSave]);
@Command([FileCloseWindow]);

Save the form.

We have to create a new view. Go to designer and you may see an untitled view already exists there. Open the view and name it as All in view properties. Create two columns with title as Name and Age respectively. Select the column values as EmployeeName and EmployeeAge.

Create a view action. Name it as Create Employee. Write the formula as

@Command([Compose]; "Employee")

 What is OLE? OLE stands for Object Linking and Embedding developed by Microsoft for dynamically linking and embedding different OLE objects. Lotus Notes client is an OLE automation server and controller so that we can invoke Excel objects within Notes.

Go to Domino designer and open view All. Create another view action named Generate Excel Report.In the run pull-down list select client and then LotusScript. Write the following code snippet.

Dim session As New NotesSession
Dim db As NotesDatabase
Dim collection As NotesDocumentCollection
Dim colDoc As NotesDocument
Dim obj As Variant
Dim workBook As Variant
Dim workSheet As Variant
Dim count As Integer
 
Set db=session.CurrentDatabase
Set collection=db.UnprocessedDocuments
If collection.count>0 Then
Set obj=createObject("Excel.Application")
Set workBook=obj.workbooks.add(1)
Set workSheet=workBook.sheets(1)
obj.visible=True
'give heading
workSheet.range("A1").value="Name"
workSheet.range("A1").Font.FontStyle="Bold"
workSheet.range("B1").value="Age"
workSheet.range("B1").Font.FontStyle="Bold"
'iterate through documents
count=2
Set colDoc=collection.GetFirstDocument
Do Until colDoc Is Nothing
workSheet.range("A"+Cstr(count)).value=colDoc.EmployeeName(0)
workSheet.range("B"+Cstr(count)).value=colDoc.EmployeeAge(0)
Set colDoc=collection.GetNextDocument(colDoc)
count=count+1
Loop 
End If

Save the view. Open the view in Notes, selects some Employee documents and click on action “Generate Excel Report”. If everything went smoothly you will get data in Excel. Now let’s walk through the code.

First section is self explanatory. Declaring variables. obj, workBook and workSheet are declared as variants which are used for OLE objects. UnprocessedDocuments within view context refers to selected documents. If user selects some Employee documents from view, code within If—-End If is executed.

createObject("Excel.Application")

invoke a new Excel object

Set workBook=obj.workbooks.add(1)

add a workbook to excel

Set workSheet=workBook.sheets(1)

refer to sheet 1 in excel

Now make the excel object visible, obj.visible=True

Add heading in first and second cells and make them bold

workSheet.range("A1").value="Name"
workSheet.range("A1").Font.FontStyle="Bold"
workSheet.range("B1").value="Age"
workSheet.range("B1").Font.FontStyle="Bold"

Now iterate through selected documents and export field values to excel

count=2
Set colDoc=collection.GetFirstDocument
Do Until colDoc Is Nothing
workSheet.range("A"+Cstr(count)).value=colDoc.EmployeeName(0)
workSheet.range("B"+Cstr(count)).value=colDoc.EmployeeAge(0)
Set colDoc=collection.GetNextDocument(colDoc)
count=count+1
Loop

Here count is used to dynamically select cells. I hope this gives foundation for OLE programming in Lotus Notes. For further reference look at

http://msdn.microsoft.com/en-us/library/bb149081.aspx

Cheers!