I recorded a macro in Excel to get the VBA code so I could translate it to LotusScript, and this is what I got:
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()
xlWkBook.InactiveListBorderVisible = False
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