Month: March 2006

Microsoft Office products like Word, Excel and Project can be accessed by LotusScript code running on a Windows machine through the OLE interface made available by the CreateObject function:

A handle to an OLE root object is a simple and mighty link to (almost) every property and method of the interfaced application.

Certainly not reachable are the constants, and I cannot guess any reason for this. MS Project 2003 has some 3,800 constants defined. All of them are well described in the online manual, but none of them is ever linked to any of the objects reachable from the root object. They are available from insibe the VBA macros, simply because MSProject is the object library and the VBA runtime generates an object for it. So the constant pjTimescaleDays, which is a member of the enumeration PjTimescaleUnit, is defined by the path

MSProject.PjTimescaleUnit.pjTimescaleDays

In fact VBA programmers are more lucky, because the VBA runtime makes an alias for each constant so that the last identificator is enough.

In my programs I could put Const pjTimescaleDays = 4, it’s just a matter of copy and paste from the Object Browser integrated in the VBA IDE. But this procedure can quickly become annoying, because the whole corpus of help articles and examples heavily rely on the VBA runtime aliasing feature. And if a Notes agent needs access to just a 10% of all of the available constants, then it’s really cumbersome to manually build the 400 lines of constants. And they are constant in a subtle way, i.e. until the product vendor suddenly change them.

To access constants defined in OLE applications the VBA Object Browser uses the Tlbinf32.dll library. Luckily enough this library is just another OLE application which can be directly used in LotusScript code.
It officially ships with Visual Studio, but if you haven’t got it already (it sould be in the windows system32 folder), you can download the Tlbinf32.dll library from afreedll.com.

a good article about the Tlbinf32.dll library
a useful manual for the Tlbinf32.dll library

Example

To show how you can load OLE constants and use them to effectively simplify and make your code selfdocumented, here is a simple agent for importing MS Project tasks into Notes documents.

Block 1: How to load OLE constants

Block 2: How to find the path to the object libraries

Block 3: How to import many Project fields at once

Installing the Tlbinf32.dll library

Before using its services, the Tlbinf32.dll library needs to be properly installed. This is done by putting its file in the windows system32 folder and then by registering it. This means that the user has to have administrator rights to their PC. As usual, you can implement the auto install feature for a needed file in a Notes database in three steps:

1. Attach the file to the About Database document and hide its paragraph
2. Add the @Command( [RunAgent]; “(Install Tlbinf32.dll)” ) to the Post Open event of the Database Script
3. Add the following agent to the database and name it (Install Tlbinf32.dll)

I use many lists in my programs. They are handy for keeping it tidy and configurable. I surely could put a constant in its own identificator and sometimes I do. But what if I need two or more similar constants? Lists are definitely the best choice for holding column numbers and for exchanging fields between Office and Notes documents.

Sometimes double lists would be better than normal lists. Example: an agent for importing some fields from an Excel book with many sheets into a Notes document using a double list for declaring the import structure. It should work this way:

With such a structure one could define the import as a simple forall loop:

Unfortunately double lists don’t exist in LotusScript, so there is no chance for that code to run in a Notes system.

Here is my implementation of double lists, in its own “DoubleLists” script library. Its use is very similar to the one described earlier, but this is working code.

The important thing here is the flexibility of this approach, something similar to the Prolog programming language. The import machine is known and fixed, but by means of a simple set of declarations it can import any book. And adding a new field to import is just a matter of copy and paste a declaration and adjust it, as well as stopping the import of a field is just a matter of commenting out a declaration. And the program gets self documented. These are the reasons why I use lists and double lists as much as possible.