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:
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 w
To continue reading for free, register below or login
To read more you must become a member of SearchDomino.com
ork, 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:
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.
—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:
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.
