As I haven’t found a good demo in this forum how to export Lotus Notes data to Openoffice Calc spreadsheets, I have created a short demo for you. What my demo agent does. It goes through a notes view and exports some fields of each notes document into a table row in openoffice calc.
You can choose if the user should be able or not to see the OpenOffice application while the export agent is running. Once the OO file is ready it will be saved to harddisk.
My example requires a oo calc file that will be used as a template. This template will be filled with data comming from notes. (Google for examples, if you prefer to create a empty file rather than using a preformatted one like I prefer.)
To test my demo you first need to create the template file on your C: drive. Create a simple empty .ods file. My example requires that there is a at least one table in the file that has the name “Sheet1”.
Enjoy the demo !
Frank Strunzkus
Option Public
Option Declare
Class OOProperties
'I found this class in the OpenOffice forum and found it very usefull. Thanks for sharing it!
'This clase can be used to pass properties to OO Objects
'E.g. pass the Hidden=True property to the app object to run the application in the background
'If you do not have to set properties, you do not mandatory need this class in your code
Private vProp() As Variant
Private app As Variant
Private vStruct As Variant
Private bInz As Integer
Sub new()
Set App = createobject("com.sun.star.ServiceManager")
Set vStruct = App.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
End Sub
Sub addProperty(sName As String, vValue As Variant)
If bInz Then
Redim Preserve vProp(Ubound(vProp) + 1)
Else
Redim vProp(0)
End If
bInz = True
vStruct.Name = sName
vStruct.Value = vValue
Set vProp(Ubound(vProp)) = vStruct
End Sub
Property Get Values()
If Not bInz Then
Me.addProperty "Dummy!", 0
bInz = True
End If
Values = vProp
End Property
End Class
Sub Initialize
Dim session As New NotesSession
Dim ws As New NotesUIWorkspace
Dim fileOo As String
Dim args() As Variant
Dim prop As New OOProperties
Dim App As Variant
Dim objCoreReflection As Variant
Dim objDesktop As Variant
Dim objDocument As Variant
Dim objSheet As Variant
Dim cell As Variant
Dim db As notesdatabase
Set db = ws.CurrentDatabase.Database
Dim view As NotesView 'The notes view that contains the data to export
Set view = db.GetView("allpersons")
Dim doc As NotesDocument 'A notes document that contains data to be exported
Set doc = view.GetFirstDocument
'The filename of a file that should be used as a template.
'Using a template allows you to define columnheaders, set colors and add logos etc. in advance.
'You can even prepare charts that display your data from Notes graphically
'I normany use a function to export the template file from my notes database to the filesystem first.
'Remember that Openoffice uses / not \ you might have to convert your filepath.
fileOo = "C:/MyEmptyTemplateFile.ods"
'Create objects that are required to work with openoffice
Set App = createobject("com.sun.star.ServiceManager") 'The servicemanager
Set objCoreReflection= App.createInstance("com.sun.star.reflection.CoreReflection")
Set objDesktop = App.createInstance("com.sun.star.frame.Desktop") 'The main desktop object
prop.addProperty "Hidden", True 'Decide to run Openoffice hidden in background or visible to the user
Set objDocument = objDesktop.loadComponentFromURL("file:///"+fileOo,"_blank", 0, prop.Values) 'Load the file
Set objSheet = objDocument.Sheets.getByName("Sheet1") 'Define which Sheet to process. This sheet must exist in the template
objDocument.CurrentController.setActiveSheet(objSheet)
Dim rowposition As Integer 'current spreadsheet row
Dim colposition As Integer 'current spreadsheet column
rowposition = 3 'Note that the array starts at 0, to adress the first spreedsheet row 1 set rowposition = 0
While Not doc Is Nothing
colposition = 0
Print "Preparing Spreadsheet, please wait! Processing "+ doc.Lastname(0)
Call InsertSpreedsheetCellValue (objSheet, colposition ,rowposition, doc.Firstname(0))
Call InsertSpreedsheetCellValue (objSheet, colposition ,rowposition, doc.Lastname(0))
rowposition = rowposition+1
Set doc = view.GetNextDocument(doc)
Wend
Call objDocument.Store
Call objDocument.close( True )
Msgbox "Your file is ready! "+fileOo
End Sub
Sub InsertSpreedsheetCellValue (objSheet As Variant, colposition As Integer, rowposition As Integer, fieldvalue As String)
Dim cell As Variant
Set Cell = objSheet.getCellByPosition(colposition, rowposition)
Cell.String = FieldValue
colposition = colposition+1
End Sub