Domino Code Fragment

Code Name*
Export a Lotus Notes document to excel (creating the excel spreadsheet) 		Date*
05/05/2025		Source (or email address if you prefer)*
[email protected]
IP address:.172.70.126.34
Description*
We use the following code to export a lotus notes document to excel
(creating the excel spreadsheet).		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:
The code is run from an action button.
We use a Excel template to create the same layout as our Lotus Notes
document. We even create a pie chart automatically based on the column values.

Dim w As New notesuiworkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Dim Doc As NotesDocument
Dim notesformula As String
Dim xlApp As Variant
Dim xlsheet As Variant
Dim xlChart As Variant
Dim uidoc As notesuidocument
Dim ExcelFileName As String

Set uidoc = w.currentdocument
Set doc = uidoc.document

Set db = Session.currentdatabase

Dim dbPath As String
Dim serverPath As String
serverPath = GetServerPath("CTT Templates")
dbPath = GetDirPath("CTT Templates")
Dim dbPay As New NotesDatabase(serverPath, dbPath)

Set xlApp = CreateObject("Excel.application")
xlApp.Workbooks.Open(dbPath &"expense.xlt")

xlApp.Visible = False

Set xlsheet = xlApp.Workbooks(1).Worksheets(1)

With xlsheet
.Range("A1").Value = "Financial Overview by Expense Type
(Reporting Period: " & doc.ShowDate(0) & ")"
.Range("B2").Value = doc.year1(0) & " (A)"
.Range("B4").Value = doc.a1(0)
.Range("B5").Value = doc.b1(0)
.Range("B6").Value = doc.c1(0)
.Range("B9").Value = doc.e11(0)
.Range("B10").Value = doc.f11(0)
.Range("B11").Value = doc.g11(0)
.Range("B12").Value = doc.h11(0)
.Range("B13").Value = doc.i11(0)
.Range("B14").Value = doc.m11(0)
.Range("C2").Value = doc.year2(0) & " (B)"
etc......
End With

ExcelFileName = "c:\" & Cstr(Year(Now)) & Cstr(Month(Now)) &
Cstr(Day(Now)) & " " & Cstr(Hour(Now)) & Cstr(Minute(Now)) &
Cstr(Second(Now)) &".xls"
Print ExcelFileName
xlapp.activeworkbook.saveas ExcelFileName
xlApp.Quit

Call doc.Save(True, True)
uidoc.EditMode = True
Call uidoc.GotoField("Excel")
Call uidoc.FieldClear( "Excel" )
Call session.SetEnvironmentVar( "ExcelName", ExcelFileName)
Call uidoc.refresh