Create Excel Lists Using LotusScript

I have created some code – as many others have over time – to export a Notes view to Excel. The code creates the Excel application object, a workbook and a worksheet and then populates the new worksheet with data from the view. The twist to this request was that once the export was done, they wanted to have some of the columns use the List function of Excel; that is, they wanted to be able to filter the columns based on the values in them.

I recorded a macro in Excel to get the VBA code so I could translate it to LotusScript, and this is what I got:
Range("I1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$I:$I"), , xlYes).Name = "List1"

Uh oh. How do I pass parameters to the Add method and set the value for the Name property in the same line? I remembered from past experience, this type of code is tricky to do in LotusScript. Searching didn’t reveal where anyone had done this exact thing before but I found an example that led me to the solution. The code is (I found that xlSrcRange and xlYes both have a value of 1):

 Set xlApp = CreateObject("Excel.Application")
 Set xlWkBook = xlApp.Workbooks.Add()
 Set xlWkSheet = xlWkBook.Worksheets.Add()

 With xlWkSheet.ListObjects
  xlWkSheet.Range("I1").Select
  Call .Add(1,xlWkSheet.Range("$I:$I"),,1)
 End With
 xlWkBook.InactiveListBorderVisible = False

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

The xl* objects are defined as variants. The last line turns off the borders for the inactive lists. Note that I didn’t worry about naming the list as the VBA script does. I’m not sure if that has any long-term impact or not. The actual code also duplicates the two lines in the With clause for three other columns.

When the Excel file opens, the column headings are bolded and, when you click in the column, an arrowhead appears. Click on that and it gives you the unique values in the column plus a few default options (eg. All, Top 10, blanks, etc.).

It isn’t perfect since I haven’t figured out how to keep the border hidden when they click on a filtered column nor how to make the arrowhead that indicates a filter is present be visible at all times. But, considering I wasn’t even sure how to do it in the first place, I’ll call it a success.


Technorati: Show-n-Tell Thursday

SnTT: One Thing I've Learned

A few months ago, I developed a new interface to consolidate a number of our applications into what would look like a single-database application. The main part of the interface is a frameset in the “portal” database that consists of a top navigator frame and a bottom “main content” frame. The hotspot links in the top frame execute LotusScript code to write an environment variable to notes.ini that indicates the database server and path, element type and element name that should be opened, then targets the main content frame and opens a single-frame frameset whose content is computed using the environment variable.

Still with me? Anyway, this has worked really well with just a few oddities. Object focus is an issue sometimes, and I can’t get everything that opens in that frameset to update the window title properly (they say “Untitled” instead of the name of the application and view).

But the biggest problem I had was that some of the applications have buttons to create documents in the background and then display them when the build process is done. This is when the loss of focus becomes an issue. Notes seems to just lose track of where it is and does what appears to the user to be a random change of the view. Plus they don’t get the document (report) they are expecting. I can only attribute this to the nesting of objects (the code is in a button, which is in a view, which is in a frameset, which is in an application, and that application is contained in a frameset in another application).

Still there? When the document is built, I use NotesUIWorkspace.EditDocument to open it so the user can see it. The solution to the random (incorrect) behavior is to add the line Call NotesUIWorkspace.SetTargetFrame(“_blank”) before the EditDocument command. This forces the new document to open in a new tab. Plus, it works whether the application containing the code is open within my portal database or in its own tab, as if it were opened from a bookmark or the workspace.

I’d post a picture of the portal application, but I’m not sure I’m allowed yet. I will try to get a mockup of something I can post since that may help this make more sense.

Technorati: Show-n-Tell Thursday