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

Does this make me a bad person?

I don't think I could care any less that David Blaine's next stunt could leave him blind. And no, I'm not going to link to anything - his 15 minutes should have run out a year ago.

Phone Goodness

I really enjoy my phone. It’s not a Blackberry, but it’s close enough for me. I’ve had my LG Rumor for about 3 months now and find it can do just about everything I want it to. It doesn’t have push email but I don’t get that much important stuff that I need to know immediately when I get a message. The keyboard is great for sending messages and texts (although I’m still not very fast at it because my thumbs are big). I can access Facebook from it and can send Twitter messages and receive Twitter direct messages (that’s all I want at this point). I downloaded the Gmail and Google Maps apps and they work really well. It can play music and videos and takes decent pictures and accepts a micro-SD card for added space. It’s a “smart phone” without being a “smartphone” (which would cost more each month).

I wish it could upload pictures to Facebook or let me send them via email, but I think that is more of a Sprint thing than a phone thing.

I also got an Invisible Shield to protect the screen. VERY worth the investment in my mind.

I don’t know why I wanted to post this. I guess just to say I appreciate the constant advances in phones.

Summer Vacation

Gregg has been bugging me (as friends will do) to post about my own Michigan summer vacation adventure since we went to one of the same places he did. We also went to Mackinac Island but were able to spend more time than Gregg’s family did. It is an annual trip for us (8 of the last 9 years) but this year was different because my Mom and my sister and her family were there with us.

We went up on Sunday and stayed a night in Mackinaw City. That would let us decompress from the trip and the kids could swim in the hotel pool. Plus it would get us over to the island earlier and in a less stressed state. We took Shepler’s over because they tag the bags and send them directly to our hotel: The Island House. We’ve found Shepler’s to be the easiest for us to deal with (but the Arnold jet-powered catamarans are pretty cool), and we love the bed and breakfast package at the Island House (they have a really good breakfast buffet).

Since we’ve been there so often, we definitely have a routine. We brought lunches for everyone and ate in Lafayette Park (at the base of the Fort), then the kids played on the small playground that is there. We got three adult bikes (we brought the other six bikes with us) and started around the island for the first time. Our daughter just learned how to ride on two wheels and she was excited to be riding around on her own. We all rode around the island 5 times while we were there and she made it between 2 and 3 miles on her own each time – pretty good for a 5 year old! Then I got to pull her using the Trail-Gator (and answer the usual dozen questions about “where did you get that?”).

We have a spot we stop about halfway around the island so we can rest and stretch. And skip stones. Find a flat, round stone of reasonable weight, snap your wrist and see how many times it skips. Tuesday was probably the best day because the water was so calm. I think I hit 15 skips with one stone. Wednesday was fun too because it was really wavy, so we tried to skip from one wave to the next.

Dinner one night is at the Ice House, the casual restaurant at the back of the Island House. It is good food and reasonably priced – plus it is out of the way so is not very busy. The second night, we go to 3 Bros. Sarducci for deep dish pizza. Really good stuff! Then, of course, there is fudge!

We didn’t go up to Arch Rock this year. We just didn’t all feel like climbing the hills. But I took our daughter through Fort Mackinac for the first time. I hadn’t been in MANY years, so it was interesting to me too. The kids even participated in a bit of a marching drill. Pretty funny watching everyone try to keep up.

We have a good time every year we go up. Gregg will attest than I have lots of information to share about Mackinac. I’m glad to share if you want to take a trip yourself.

Weird @TextToTime Behavior

This isn't really a Show-n-Tell Thursday post, even though it is Thursday. I'm having a problem with a @TextToTime formula and I can't find any documented reason why. When I pass it the string "R. Marshall" it returns 3/1/2008. In fact, if I pass it "Ma", it returns that date. As long as it doesn't have "ma" at the start of any word in the string, it returns nothing (which is what I'd expect).

Has anyone else seen that? Can you duplicate it?

Don't Do This in Notes 8

I started writing this the other day but gave up because I wasn't sure what caused this to happen. I'm still not positive, but I have a better idea.

I'm running Notes 8.01 on Windows XP SP2. If I turn on the LotusScript debugger, go to my client and open a composite application, like my mail database on the 8 template, I get a debugger window. So far, so good. If I say "Oops, I didn't want the debugger for that" and click the Continue button, the debugger is replaced with a window of my first mail message. That has a title bar and a menu bar but no toolbars, and there is no way to make it go away. I have to kill the client processes and restart to fix it.

This only seems to happen (so far) with a composite app. And I haven't done exhaustive testing (because I'm trying to get my projects done!) to know if there is something specific that is a trigger or if it is just something weird about my installation (see my last post for Sametime oddities).

I'd be interested to know if anyone else can reproduce this problem.

Have You Seen This in Notes 8.0.1?

We have a few people who have moved to the Notes 8.0.1 client and are planning the rollout to the rest of our clients. However, we have been seeing some problems in the client that we can't explain and want to try to sort out before continuing on.

The machines we have upgraded are our developer/admin machines, so they had the Notes, Admin and Designer clients installed (all 7.0.2). We did an upgrade rather than uninstalling 7/installing 8 and none of them had an 8 beta installed. These are all Win XP machines. We also had Sametime Connect 7.5.1 installed and I'm not sure if that was uninstalled before installing 8. The one thing we may not have done that is in the installation guide is to stop all other Windows programs before installing.

What we are seeing is that the embedded Sametime client does not always start correctly. It will be OK sometimes, but other times will not login and will not allow us to login. Other times it will login but the system tray icon will show as "disconnected". Sometimes when logged in, it will not open a chat window. Restarting the Notes client usually resolves the issues.

We can't figure out if it is something to do with how the client was installed on these machines, if it is related to how our environment (OS) is secured or something else. We did have local admin rights on the machines when we installed the software.

We're worried about the impression these kinds of things will leave, and we really have no desire to remove all traces of 7 before installing 8 (that seems to resolve the problems) because of the extra work it will cause everyone in setting up their Notes environment again. Any thoughts would be appreciated - no idea is too crazy at this point.

Shatner Really *is* Speaking at an IBM Conference

Got an email today about the Rational Software Developer Conference and saw that William Shatner is one of the keynote speakers. He's been rumored to be at Lotusphere so often that it's become cliche.

I've enjoyed the music at the Lotusphere OGS the last couple of years but I'm a little jealous that the Wallflowers are playing the RSDC. Maybe next year....