About This Code
Brief Description:
Export to Excel
Category:
Application, Lotusscript
Notes Version:
R5.x, R6.x
Last Modified:
07 May 2004
OpenNTF Disclaimer
All of the program code and information presented in the OpenNTF.org Code Bin are provided "as-is", and should be used at your own risk. OpenNTF.org make no express or implied warranty about anything in the Code Bin, and OpenNTF.org will not be responsible or liable for any damage caused by the use or misuse of anything from this site. OpenNTF.org makes no guarantees about anything. Please thoroughly test all of the knowledge and code you find here before you attempt to use them in your production environment.
Code / Description
Export-Script
Easy to use & implement script to export views to Microsoft Excel
================================================================================
This Script has been created by D. Hasa, Yel GmbH, Switzerland in April 2001
It may be distributed and modified freely, as long as this header is kept intact.
Please report any bugs, fixes or enhancements to d.h@yel.ch
This script exports a UIView 'As-Is' from Notes 5/6 to Excel 2000/XP
It has been tested with Notes 5.0x/6.x into Excel97, 2000 & XP
--> every column (include headers) is a column in Excel
and every value displayed of a document is a row in Excel
Every Value will be inserted as Text into Excel
Updates:
================================================================================
30.11.01
SELECTED DOCUMENTS
You can now export also only selected documents, but the script gets thru all documents in a view,
because the the property doc.ColumnValues(n) only returns a value, if it has been fetched from a view
(selected documents get fetched by a NotesDocumentCollection).
----
Excel-Object Problems
Added another ExcelApp-Constant (Excel.Application.8)
----
Visualised Progress
This script is From http://www.notes.net/50beta.nsf/7d6a87824e2f09768525655b0050f2f2/1B5AFDF4B4ACC732852566BB005CDC45?OpenDocument
Thanks to Les Szklanny
--> I cannot give you any guaranty of proper functionality you can turn it on or of --> const visualproc
================================================================================
14.01.02
================================================================================
Changed Error-Handling on ExcelObject Create
================================================================================
09.03.02
================================================================================
- Removed Form1..4 from Formatting (does not exist anymore)
- Added Constant for Papersize
- If titbar-rotate = 0 then autofit from line 1 else from line 2
================================================================================
02.07.02
================================================================================
- Removed Error with 'count'-columns
30.07.02
- Changed bug if only one doc is selected (Thanks to A. Migliore)
================================================================================
14.12.02
================================================================================
- Added ability to export multivalue columns (Functions ListText and ReplaceSubString)
================================================================================
01.11.03
================================================================================
- Changed force 'Text'-Export
- Trim for all values
- Ask for Exporting ResponseDocs
- Retrieve and keep numbervalues (asked to convert to text)
================================================================================
Usage / Example
Implementation
It is only a script without any Dialog-Boxes by exception --> Distribution and Implementation is very easy
- Copy the whole code (Open in Notepad, Edit Select All - Copy)
- Create a new Agent
- Set the following settings
R5:
R6:
- Switch the code to Lotus Script:
- Mark 'Option Public' and paste the before copied code from the clipboard (Edit-Paste)
--> The Export Agent is now available in every view of that database
Code Attachments
Comments
Posted by Kim L Scott on 07/13/2004 11:35:09 AMHow do I use custom templates?
Or is this possible? How do I change the code from pointing to "Classic" or "Simple", etc., etc, to execute my custom Excel Templates?
Thanks,
Posted by Daniel Hasa on 07/13/2004 12:03:37 PMYou might, but it's not recommended...
Hi!
You'll need to add those templates to the functions "getAutoForm" and "SetSelList" and append the amount to the selList-Variable
Problem: Excel does use an internal code for those templates: "Simple" is "-4154", "Classic1" is "1"...
Would not recommend it, because it might be a problem between the different Excel-Versions...
Posted by Joshua A Hesson on 07/03/2006 09:35:46 AMSave excel and attach to a notes doc in db
I would like to have the agent save the excel spreadsheet & then create a new document in the database the data was exported from & attach that created spreadsheet onto the document...any assistance/pointers would be greatlly appreciated!
Thanks! :)
Posted by Daniel Hasa on 09/26/2006 02:21:30 AMRe: Save excel and attach to a notes doc in db
Sorry just saw your comment for the first time (notification did not work...):
It's simple:
in 'Initialize' replace the line (just before 'Exit Sub')
excelAppObject.Visible = True
by the following
Delete RefreshProgress
stNewName = "c:\"+db.Title+"_"+Cstr(Today)+".xls"
excelAppObject.ActiveWorkbook.SaveAs(stNewName)
excelAppObject.Quit
Dim ndb As New NotesDatabase("","")
Dim rti As NotesRichTextItem
Call ndb.Openmail()
If ndb.IsOpen Then
Set docNew = ndb.CreateDocument
docNew.From = "Memo"
Set rti = New NotesRichTextItem(docNew,"Body")
Set object = rti.EmbedObject(1454, "", stNewName)
Call docNew.ComputeWithForm(True, False)
Call docNew.save(True,False)
Kill stNewName
Set uidocNew = workspace.EditDocument(True, docNew)
Else
Messagebox "Export has been saved to '"+stNewName+"'!", 64,"[Info]"
End If
Now you can replace the ndb by any database you want (remove then the line ndb.Openmail), set any form and field
Posted by Shep Cockcroft on 08/21/2007 10:29:37 AMCalculated columns in the view?
Hi I tried your utility and it works just fine, except that I'd like the contents on a calculated (total) column included in the exported spreadsheet. Is this possible to include?