This and that...

This and that...

Code exports Lotus Notes data to an OpenOffice Calc spreadsheet

Notes ProgramingPosted by Martin Tue, February 17, 2009 18:12:58

Many organizations currently prefer to use free OpenOffice.org over the Microsoft Office suite due to licensing issues and fees. While Lotus Notes users can export Lotus Notes data from a database to a Microsoft Excel spreadsheet, the same code won't work in OpenOffice Calc spreadsheets.

The following LotusScript code will export information from a Notes database to an OpenOffice Calc spreadsheet.

Note: Place this code in a button in a Notes view or as an agent to export the required data.

Sub Click(Source As Button)

''==''==''==''=='' Code For Creating Office Application
Automation Object ''==''==''==''==''
Dim SM As Variant
Dim Desktop As Variant
Dim args() As Variant
Dim calApplication As Variant
Dim worksheet As Variant
Dim cell As Variant

'' Creating Handle To OpenOffice Object
Set SM = CreateObject("com.sun.star.ServiceManager")

'' Create The Front-End Reference
Set Desktop = SM.createInstance("com.sun.star.frame.Desktop")

'' Opening The Excel Application On Desktop Giving Focus
Set calApplication =
Desktop.loadComponentFromURL("private:factory/scalc", "_blank" , 0 ,
args)

'' Setting Sheet Name
Set worksheet = calApplication.Sheets.getByName("Sheet1")

''Set cell = worksheet.getCellByPosition(0,0)
''cell.setString("Testing Transport To OpenOffice")
''==''==''==''=='' Code For Creating Office Application
Automation A Object ''==''==''==''==''

Dim uiws As New NotesUIWorkspace
Dim uiview As NotesUIView
Dim view As NotesView
Dim doc As NotesDocument

Dim counter As Integer
Dim xlr,xlc,loops As Integer
Dim cell1,cell2,cell3 As Variant

Set uiview = uiws.CurrentView
Set view = uiview.View

loops = view.AllEntries.Count

Set doc = view.GetFirstDocument

Dim i As Integer
i=0
For i=0 To loops-1
'' '' Exporting Notes-Data Code To OpenOffice
Calc Applicaiton
Set cell1 = worksheet.getCellByPosition(0,i)
Set cell2 = worksheet.getCellByPosition(1,i)
Set cell3 = worksheet.getCellByPosition(2,i)

cell1.setString(Cstr(doc.EDForwardID(0)))
cell2.setString(Cstr(doc.EDEmpMailID(0)))
cell3.setString(Cstr(doc.EDEmpPh(0)))

If(i<loops-1) Then
Set doc = view.GetNextDocument(doc)
End If

Next

worksheet.Columns.Autofit


End Sub