This and that...

This and that...

How to export Lotus Notes views to a Microsoft Excel database

Notes ProgramingPosted by Martin Tue, February 17, 2009 18:18:31

Exporting data from a Lotus Notes view to a Microsoft Excel database is a simple process that doesn't require object linking and embedding (OLE) and is easily customizable. To begin, create a LotusScript agent that will retrieve all data from a Lotus Notes view and export it to Microsoft Excel. Lotus Notes users can then format the Excel spreadsheet to their specifications for reporting purposes.

This process requires a single LotusScript agent and one shared action to call it. Place the shared action in the Lotus Notes view that you want to export. The trick is to write a text file using HTML table formatting and save the file with a Microsoft Excel extension (.XLS). Next, you can send email messages to Lotus Notes users that include their reports.

When a user opens the file, Microsoft Windows will open Excel. The program then detects that the file is an HTML table and imports it into Microsoft Excel using the specified HTML formatting.

Sub Initialize

'I placed all of the code in a single sub

(normally I split it all out... but for this demo

purpose, I just put all code in one sub)

Dim doc As NotesDocument

Dim uidoc As NotesUIDocument

Dim tmpdir As String

Dim uiview As NotesUIView

Dim view As NotesView

Dim dc As NotesDocumentCollection

Dim ret As Integer

Dim session As NotesSession

Dim db As NotesDatabase

Dim workspace As NotesUIWorkspace

Dim tmpdatafilename As String

Dim tmpdatafilenumber As Integer

Dim memodoc As NotesDocument

Dim rtitem As NotesRichTextItem

Dim object As NotesEmbeddedObject

Set session = New NotesSession

Set workspace = New NotesUIWorkspace

Set db = session.CurrentDatabase

tmpdir = session.GetEnvironmentString

("Directory", True)

tmpdatafilenumber% = Freefile()

tmpdatafilename = tmpdir & "HRReport.xls"

'Open a file for output

Open tmpdatafilename For Output As


Set uiview = workspace.CurrentView

Set view = uiview.View

'Print to the file some basic CSS for easy

formatting later if the users want a change

to the default.

Print # tmpdatafilenumber%, |



<TITLE>Table example</TITLE>

<STYLE type="text/css">

TABLE { background: #ffffff; border: solid black;

empty-cells: hide }

TD { border: solid black;

border-left: none;

border-right: none;

} { border: solid black;

border-left: none;

border-right: none;

background: #C0C0C0;

font-weight: bold;

font-size: 11px;

text-align: center;


TD.viewname { border: solid black;

border-left: none;

border-right: none;

border-bottom: none;

background: #C0C0C0;

font-weight: bold;

font-size: 16px;

text-align: left;


TD.salary { border: solid black;

border-left: none;

border-right: none;

background: #00FFFF;





'Heart of the code....

'Print an HTML Table and then construct

the table from the column contents

of the current view.

Print # tmpdatafilenumber%, |<table>|

'Include the view name in the top of the


Print # tmpdatafilenumber%, |<tr>|

Print # tmpdatafilenumber%,

|<td class="viewname"

colspan="| & Cstr((Ubound(view.Columns) + 1))

& |">|

Print # tmpdatafilenumber%, view.Name

Print # tmpdatafilenumber%, |</td>|

Print # tmpdatafilenumber%, |</tr>|

'Start a row of headers (couldn't get TH CSS to work,

so I created my own class called top)

Print # tmpdatafilenumber%, |<tr>|

Forall vc In view.Columns

Print # tmpdatafilenumber%, |<td class="top">|

Print # tmpdatafilenumber%, vc.title

Print # tmpdatafilenumber%, |</td>|

End Forall

Print # tmpdatafilenumber%, |</tr>|

'Now loop though all of the documents in the

view and create a HTML for each one.

'For each column, put in the cell tag.

Set doc = view.GetFirstDocument

Do While Not doc Is Nothing

Print # tmpdatafilenumber%, |<tr>|

Forall c In doc.ColumnValues

Print # tmpdatafilenumber%, |<td>|

If Isarray(c) Then 'Make sure you work with a

multivalued field.

For a = 0 To Ubound(c)

If a = 0 Then

Print # tmpdatafilenumber%, c(a)


Print # tmpdatafilenumber%, "<br>" & c(a)

End If


Print # tmpdatafilenumber%, |</td>|


Print # tmpdatafilenumber%, c

End If

Print # tmpdatafilenumber%, |</td>|

End Forall

Print # tmpdatafilenumber%, |</tr>|

Set doc = view.GetNextDocument(doc)


'Now that you have all of your data, end the

table and html tags

Print # tmpdatafilenumber%, |</table>|

Print # tmpdatafilenumber%, |</body>|

Print # tmpdatafilenumber%, |</html>|

Close tmpdatafilenumber%

'Now you can either prompt them for where the

report is on their hard drive, or you can

email it to them

'If like email....

Set memodoc = db.CreateDocument

memodoc.Form = "Memo"

memodoc.Subject = "Exported View Report"

Set rtitem = New NotesRichTextItem

(memodoc, "Body")

Call rtitem.AppendText(memodoc.Subject(0))

Call rtitem.AppendText(Chr$(10) & Chr$(10))

Call rtitem.AppendText("Open the attached file

in Excel to view the report.")

Call rtitem.AppendText(Chr$(10) & Chr$(10))

Set object = rtitem.EmbedObject _

( EMBED_ATTACHMENT, "", tmpdatafilename)

memodoc.SendTo = session.UserName

Call memodoc.Send(False)

Kill tmpdatafilename ' Delete the file from the

users hard drive if you email the report.

Messagebox "Please check your inbox for the

report", 0 + 64, "Report Complete"

End Sub