We use the following code to export a lotus notes document to excel
(creating the excel spreadsheet).
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
