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