PreviousNext…

Tip #10: simple export to Excel

Background

Anyone use that horrendous @Command([FileExport]…) thing? Worse still, does anyone recall how Lotus changed some of its parameters in the move from R4.x to R5.x! Heinous! (I did a rant about that some time ago).

Well, I'm not here to talk about that… it's of limited use in the real world anyway; most users have a more pressing need to export view data to MS Excel, rather than 1-2-3 in this day and age. Sure, we could all rant on about M$ now, but let's just face facts.

Anyway, please find the code attached. It's a basic implementation — feed the routine three arrays, and you're done. The arrays should all have the same number of elements, and represent:

  1. the columns in your new worksheet
  2. the Notes fields that these should map to, and
  3. and the width of your columns

You can spruce this code up to process multivalue fields, define additional worksheet properties, etc., etc. This is just a starter, and I hope it helps someone out there. The "Further reading" link is also useful in terms of expanding the code for your own purposes.

Example call

Oh yes, here's what a typical call would look like (here I've coded an agent which exports three bits of info from selected documents in one's mail file):

	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim strCols(2) As String
	Dim strFlds(2) As String
	Dim intCells(2) As Integer
	Dim dc As NotesDocumentCollection
	
	Set db = session.CurrentDatabase
	Set dc = db.UnprocessedDocuments
	
	strCols(0) = "From"
	strCols(1) = "When"
	strCols(2) = "Subject"
	
	strFlds(0) = "From"
	strFlds(1) = "PostedDate"
	strFlds(2) = "Subject"
	
	intCells(0) = 18
	intCells(1) = 18
	intCells(2) = 18
	
	Call exportToExcel(dc, strCols(), strFlds(), intCells())

Further reading

Downloads

Comments

  1. helpme..i need export views to excel using lotusscriptvinod#
  2. Ben,

    Love your stuff. Unfortunately, I can't get the script to work properly. It only does one line and is not processing the rest of the selected documents. In addition it is processing the line highlighted (like the caret note functionality - very weird) rather than the ticked documents.

    On the :
    Set doc = dc.GetNextDocument(doc)
    Loop

    It is exiting the sub!

    I'm a bit of a novice so if I've missed somehting obvious then please excuse me! I'm using Notes 6.0.2 and I noticed that you didn't test it on this version! Would this have anything to do with it?

    Thanks,
    SharonSharon#
  3. Sharon, check your agent trigger.

    If your code isn't set to run on "selected documents" then it will exit when you try to select the next document, as NotesDatabase.UnprocessedDocuments returns something different depending on the agent triggerBen Poole#
  4. You are a star Ben Poole. It works perfectly!
    Many Thanks,
    SharonSharon Richards#
  5. I have a problem, when i have more then 8195 +- documents, notes stop to export and then put an error message. do you know what is that?Mauro#

Comments on this post are now closed.

About

I’m a software architect / developer / general IT wrangler specialising in web, mobile web and middleware using things like node.js, Java, C#, PHP, HTML5 and more.

Best described as a simpleton, but kindly. You can read more here.

";
File Attachment Icon
exportToExcel.lss