Manage Learn to apply best practices and optimize your operations.

LotusScript library to generate Notes docs in Excel

I wanted to share a LotusScript library I developed with an OO class description that makes it very easy to use Excel to generate Excel documents.

View member feedback to this tip.

After reading Paul Bleeker's tip, Link Notes fields with Excel cells I wanted to share a LotusScript library I developed with an object-oriented class description that makes it easy to use Excel to generate Notes documents. The idea is to use an Excel file as model. In the Excel model, you can use any feature you want, create a nice table with different fonts, colors, and backgrounds and give the target cells (the cell that will receive there value from Notes) the same name as the source Notes items.

To create a new file, you only have to code a few LotusScript specs. For instance, to populate an Excel report from the documents contained in a view, you just have to code:

In the option section : 
use "ExcelReport"                           
        ' to use the library (1) 

In the initialize sub : 

sub Initialize 
Dim excelObject as Excelreport                 
       ' declare a variable for 
the ExcelReport object (2) 
Dim session as New NotesSession 
Dim db as NotesDatabase 
Dim vw as NotesView 
Dim doc as NotesDocument 

set db = session.currentDatabase 
set excelObject = new ExcelReport
("c:tempModel.xls")                ' 
create an instance using the 
Excel model file (3) 
set vw = db.getView("ThisView") 
set doc = vw.getFirstDocument 
while not doc is Nothing 
    excelObject.fillReportLine doc                      
  ' fill a line in the 
excel file with the values from the 
current doc (4) 
   set doc = vw.getNextDocument(doc) 
wend 

excelObject.preview                                       
   ' display the 
excel report in preview mode (5) 
end sub 

So, in this simple example, only five specs are needed to create a nice Excel report (as nice as the Excel model). The other lines iterate through the documents in a view.

Detailed explanations, examples and the library listing can be found on my Web site. The library can be downloaded for free from the site (that's freeware, not shareware).


MEMBER FEEDBACK TO THIS TIP

This seems to be a great contribution. I did not test it yet, but I think I will. Many thanks to Thierry for the great tip!

—Pierre B.

******************************************

This code always returns: "OLE Automation Error."

—Atul V.

******************************************

It's not easy to give an answer knowing only the error message. First, try running the code in debug mode, this could give you more info. Use File -> Tools -> Debug LotusScript before launching the application. On the debug dialog box, click Continue. When the message appears, click OK, and the debug window will highlight the specification causing the error. It will certainly be the createObject spec. If not, send another e-mail with the faulty line.

If it is the createObject spec, it means that your OLE Automation process has failed. To work, the process needs an Excel program fully installed. You can do the following to check the availability of the Excel objects. In the designer, open a LotusScript module, for instance the Excel Report library. Select the Reference tab in the left pane, the Ole Classes in the combo box. The left pane will display all of the automation classes registered on the workstation. There must be one for Microsoft Excel, named something like 'Microsoft Excel x.x Object Library.' If you don't find it, you should uninstall, and then install again Excel and check. Uninstalling the re-installing can be tried too, even if the Excel Object Library shows in the list. This happened once to me, the OLE automation suddenly stopped working (Windows mystery), and the only issues I had has been to re-install.

I also met problems on Win2k workstation, when the user was the only member of the basic users group. Putting him in the group 'users with privileges' (I'm not sure of the group name, as my computers speak French). One last idea -- don't keep Excel running in another window when you launch the program. I hope this helps!

—Thierry Seunevel, tip author

******************************************

I am creating other reports in Excel, so there is no problem creating Excel objects or accessing any properties or methods of the same. The line which is causing the error is:

Set xlRange=xlNames
(LINEFIELDNAME).refersToRange()
 and the same is from the initReport()
 subroutine.
 Private Sub initReport()
  '------------------------------------------------
-------------------
  ' initialisation champs dÉtail : 
liste des champs de la ligne dÉtail
  '--------------------------------------------------
-----------------

I am looking for a solution like the one described in this tip.

—Atul V.

******************************************

It seems that there is a problem with the value used for the LineField property. The default value for this property (internal field name = LineFieldName) is "REPORTLINE." If the LineField property is not set in the program, there must be a cell range named "REPORTLINE" in the Excel model. If the LineField property is set in the program, there must be a cell range of the same name in the Excel report.

  1. You must not give the *NONE value to the LineField property if you use the FillReportLine method.
  2. If you don't use the FilleReportLine method, give the value *NONE to the LineField property.

—Thierry Seunevel, tip author

******************************************

This is an excellent tip. I had to actually use it to understand what it was doing, but it works beautifully. I can't thank the author enough for sharing this tip. I am now able to produce professional looking reports in Excel easily, using a variety of templates I have designed.

—Maris W.

******************************************

I receive an error when I try to create a report with a hidden field. Also, if my field name contains an underscore (i.e., 'Team_Action1'), it does not work (at least that seems to be the common denominator). Are these known problems? Maybe I need to change something in the code to get these to work correctly?

I did get the code to work properly with other fields that did not contain an underscore in the name (i.e., 'Subject'). I think this is a great tip. Thanks for the contribution.

—Anonymous

******************************************

I tested these two issues myself.

I don't know exactly what you mean by "hidden field." I tried to check the hidden checkbox in the cell format property box -- it did not raise any errors. It is not possible to use computed for display Notes items, because they exist only when the document is opened in the UI.

I can't see any reason that would explain the problem of the underscore in the field name. I tried this myself before answering, and it worked for me! The only character that is excluded from field names is the ! (exclamation mark) because it is used in internal Excel range name (print area for example). Maybe there could be a problem due to different character sets between this user's workstation and the script source. What can be checked is the spec, like this one in the initReport and initHeader subroutines:

If Instr(sName , "!") = 0 Then 
   ... 

The constant searched must be the exclamation mark. If it's not, correct the spec so it does look like this.

I hope this can help.

—Thierry Seunevel, tip author

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip exchange by member Thierry Seunevel. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.

This was last published in October 2004

Dig Deeper on JavaScript for Lotus Notes Domino

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchExchange

SearchContentManagement

Close