More Excel Code: Inserting and Copying Rows

I was working on some code to export data to a pre-formatted Excel file and, as part of that, needed to be able to insert rows into a range so that the data I added would be included in formulas below it (they were column totals and data summaries). As I find typical when trying to control Excel from LotusScript, I couldn't locate examples of the code I needed. That doesn't mean they aren't out there, just that I couldn't find them.

After some experimentation and some dumb luck stumbling on information that referred to methods I wasn't aware of in the COM interface, I came up with the code below.

Sub CopyFormulas(xlSheet As Variant, rowNum As Integer, firstCol As Integer, lastCol As Integer)
 Dim x As Integer
 Dim cellVal As String
 xlSheet.Activate
 xlSheet.Cells(rowNum-1,1).EntireRow.Copy
 xlSheet.Cells(rowNum,1).EntireRow.Select
 xlSheet.PasteSpecial(7)
 xlSheet.Application.CutCopyMode = False
 For x = firstCol To lastCol
  cellVal = xlSheet.Cells(rowNum,x).Formula
  If Left(cellVal,1) <> "=" Then
   xlSheet.Cells(rowNum,x).ClearContents
  End If
 Next
End Sub

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

This takes the Worksheet object you pass it, copies the content and format of the row above your current row into your current row and then removes the contents of a defined set of columns if they are not formulas. The problems I had were that the copy was not done in the proper place and I wasn't maintaining the formulas and formatting of the row I was copying.

The copy location was solved by adding the xlSheet.Activate line. My code was writing data to three different worksheets (one each for month-to-date, quarter-to-date and year-to-date) so I found that adding that line made sure the proper sheet was active.

The formatting issue was resolved by a combination of two methods. One is .PasteSpecial(7) , where the 7 represents the xlPasteAllExceptBorders paste option. The other is the .ClearContents method. I was using the .Paste and .Clear methods initially. I suspect I could use the .Paste and .ClearContents methods, since I think the .Clear method was the culprit that was deleting the formats, but I am going to leave it the way it is since a) I know it works, and b) the .PasteSpecial method indicates what I am really trying to do.

By the way, the code to insert a row is xlSheet.Cells(row,col).EntireRow.Insert.

Enjoy! Now, how many times do you think I'll find this blog post when I search for this in the future? :)

Inheriting Field Values for Names Fields

I had a problem last week with a couple of forms that have fields that inherit values from fields on the document that is open when the new document is created. On one form, the field values were set properly, but on the other they were not. The form being created is a response-to-response form. The form that worked was a response form, so at first I thought the problem with the form that was not working was because it was a main form. I discovered that wasn't the problem when I was able to get that form to inherit a text field.

The fields I was trying to inherit were names fields. And the two "parent" forms used different names for the names I wanted to inherit. On the form that worked, the fields were named the same on that form and on the child form I was using for my new document. Aha! I changed the field name on the child form to match that of the form that didn't work, and that started working; of course, that also broke the other form that worked previously.

What I ended up doing was adding a Computed For Display field to the child form that was named the same as the field on the form that didn't work. It has a value in it when a new child is created and is blank at all other times. I then reference that field in the formula for my name field and it works for both forms.

I couldn't find anything that said Names fields inherit differently than other fields. but they apparently are different. After spending about 4 hours trying to figure it out, I figured I'd write it out here, just in case it comes up again.

Barcodes in Lotus Notes Applications

If you have ever wanted to implement barcoding in a Notes application, it may be easier to do so than you think. You really need four things to make it work:

1. A barcode font that is deployed to machines that will be printing the things (eg. paper or labels) that have the barcode on them.


2. LotusScript code to generate the field value for the barcode. Simple barcodes, like Code 39, just need start and stop characters before and after the number you are putting on a barcode, but they have a limited character set. More complex barcodes, like Code 128, require start and stop characters as well as check digits but are able to include the alphanumeric character set.


3. A barcode scanner.


4. An input form to handle the scanner’s output and cause processing to happen.

Though the application I am working on is encoding a 12-digit number, we are deploying a Code 128 font so that we will be ready if we expand the use of barcodes into an application that requires alphabetic characters as well. Scanner set up is pretty easy now, with most being able to plug and play with your computer hardware and able to be configured by scanning barcodes. They function similarly to keyboards, in that if you are some sort of input area (eg. a field or even the body of a mail message), the scanner will feed the data from the barcode into the field as though you typed it on a keyboard.

The vendor we used for the font also supplies code for many different applications, including LotusScript. However, to get the number I encoded to print as part of the barcode, I had to take their VBA script and modify it for LotusScript because their LotusScript code does not support human-readable codes. It was a pretty simple code change though. I configured the scanner to append a tab to each scan that it completed (explanation of why is below). I just plugged it in and then scanned seven codes on a sheet that came with the scanner and it was ready to go.

The Application

The processing form is displayed as a dialog box that allows the user to set some processing options before the input field is displayed. This is done with a programmable table with the options on one tab and the processing actions on a second tab, with buttons to toggle between the two tabs. This helps to ensure the cursor is in the input field when ready to scan by making it the only editable field visible.

The form has the main input field (TrackingNum) and a second, unmarked input field (ActionField) on it. By unmarked, I mean that it is a simple text field that has the Show Field Delimiters option unchecked. The scanner sends the decoded data from the barcode plus a tab character. The tab moves the cursor from the TrackingNum field to the ActionField field. The processing code is in the OnBlur event of the TrackingNum field; that code takes the number from the barcode, finds the document and updates it as necessary. The ActionField field has code in the OnFocus event to move the cursor back to the TrackingNum field, which makes the dialog ready for the next scan.

The form needed to be functional manually as well as with the scanner so the TrackingNum field is defined as a combobox and the form has a button on it that, when clicked, moves the cursor out of the TrackingNum field. The combobox lists items that are available for the user to process and can also be used to type in any number manually. The button just puts the cursor into the ActionField field, which then moves the cursor back to the TrackingNum field.

This is what the dialog looks like. Processing Messages is a reverse-chronological list of the actions taken each time an entry in the TrackingNum field is processed. I couldn’t find a lot about barcoding in Notes, and found nothing about how to actually code an application to interact with a scanner, but I did receive a hints from a couple of Lotus peeps that really helped. In the end, it was a lot easier than I expected.

Technorati: Show-n-Tell Thursday

Get Database Information for Configuration Documents

I've done a lot of work lately to make our applications more configurable. Most of it is related to having separate servers for development, QA and production, and needing to be able to change reference databases without requiring code changes at each stop in the change control process.

As part of that, I wanted to make it easier to enter the information we required for the reference databases. Sure, you could find the database and then type in the server name and path name (or replica ID), but we had so many that it became a burden to do it that way.

So I created the script below. This could be a function in a script library or could be added in the form globals.

Sub SetDatabaseInfo(actionID As String, docObj As Variant, titleField As String, serverField As String, pathField As String, repIDField As String)
%REM
 This function allows the user to select a database and set up to four fields with information from the selected database:
(0) Database Title
(1) Server
(2) File path
(3) Replica ID

Parameters are:
 actionID = 'Set' to set the field values, 'Clear' to clear the field values
 docObj = the document to be modified.  Can be a NotesDocument or NotesUIDocument
 titleField = item name for the database title
 serverField = item name for the database server name
 pathField = item name for the database path
 repIDField = item name for the database replica ID

If Cancel is chosen in the dialog, the document passed in is not changed.
The user receives an error if the file they chose cannot be opened.
%END REM
 
 Dim ws As New NotesUIWorkspace
 Dim nname As NotesName
 Dim session As New NotesSession
 Dim db As NotesDatabase
 Dim notesDataPath As String
 Dim filePath As Variant
 Dim result As Variant
 Const PROMPT_CHOOSEDATABASE = 13
 Dim fileInfo(3) As String
 Dim docObjType As String
 
 docObjType = Typename(docObj)
 If docObjType <> "NOTESDOCUMENT" And docObjType <> "NOTESUIDOCUMENT" Then Exit Sub
 
 Select Case Ucase(actionID)
 Case "SET"
  notesDataPath = Ucase(session.GetEnvironmentString("Directory",True)) & "\"
  result = ws.Prompt(PROMPT_CHOOSEDATABASE,"Choose Database","Choose the database for which you want information:","","")
  If Isempty(result) Then
   Exit Sub
  End If
  
  Set db = New NotesDatabase("","")
  If Not db.Open(result(0),result(1)) Then
   Messagebox "Error opening database; you may not have access to it",48,"Error"
  End If
  filePath = Ucase(db.FilePath)
  If result(0) = "" Then
   filePath = Strright(filePath,notesDataPath)
  End If
  fileInfo(0) = db.Title
  fileInfo(1) = result(0)
  fileInfo(2) = filePath
  fileInfo(3) = db.ReplicaID
  
  If fileInfo(0) <> "" Then
   If docObjType = "NOTESDOCUMENT" Then
    If titleField <> "" Then
     If docObj.HasItem(titleField) Then Call docObj.ReplaceItemValue(titleField,fileInfo(0))
    End If
    If pathField <> "" Then
     If docObj.HasItem(pathField) Then Call docObj.ReplaceItemValue(pathField,fileInfo(2))
    End If
    If repIDField <> "" Then
     If docObj.HasItem(repIDField) Then Call docObj.ReplaceItemValue(repIDField,fileInfo(3))
    End If
    If serverField <> "" Then
     Set nname = New NotesName(fileinfo(1))
     If docObj.HasItem(serverField) Then Call docObj.ReplaceItemValue(serverField,nname.Abbreviated)
    End If  
   Elseif docObjType = "NOTESUIDOCUMENT" Then
    If titleField <> "" Then
     If docObj.Document.HasItem(titleField) Then Call docObj.FieldSetText(titleField,fileInfo(0))
    End If
    If pathField <> "" Then
     If docObj.Document.HasItem(pathField) Then Call docObj.FieldSetText(pathField,fileInfo(2))
    End If
    If repIDField <> "" Then
     If docObj.Document.HasItem(repIDField) Then Call docObj.FieldSetText(repIDField,fileInfo(3))
    End If
    If serverField <> "" Then
     Set nname = New NotesName(fileinfo(1))
     If docObj.Document.HasItem(serverField) Then Call docObj.FieldSetText(serverField,nname.Abbreviated)
    End If
   End If
  End If
  
 Case "CLEAR"
  If docObjType = "NOTESDOCUMENT" Then
   If titleField <> "" Then
    If docObj.HasItem(titleField) Then Call docObj.ReplaceItemValue(titleField,"")
   End If
   If pathField <> "" Then
    If docObj.HasItem(pathField) Then Call docObj.ReplaceItemValue(pathField,"")
   End If
   If repIDField <> "" Then
    If docObj.HasItem(repIDField) Then Call docObj.ReplaceItemValue(repIDField,"")
   End If
   If serverField <> "" Then
    If docObj.HasItem(serverField) Then Call docObj.ReplaceItemValue(serverField,"")
   End If  
  Elseif docObjType = "NOTESUIDOCUMENT" Then
   If titleField <> "" Then
    If docObj.Document.HasItem(titleField) Then Call docObj.FieldSetText(titleField,"")
   End If
   If pathField <> "" Then
    If docObj.Document.HasItem(pathField) Then Call docObj.FieldSetText(pathField,"")
   End If
   If repIDField <> "" Then
    If docObj.Document.HasItem(repIDField) Then Call docObj.FieldSetText(repIDField,"")
   End If
   If serverField <> "" Then
    If docObj.Document.HasItem(serverField) Then Call docObj.FieldSetText(serverField,"")
   End If
  End If
  
 End Select
End Sub

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

The comments at the top of the code explain pretty well what is going on: pass it the action you want to take, the field names you want to update and the object containing those fields and it does the work. I honestly don't remember why I wrote it to be able to update either a NotesUIDocument or a NotesDocument, since I think I always pass it a NotesUIDocument, but it gives you the flexibility to pass either object type.

Technorati: Show-n-Tell Thursday

Configurable Embedded Views (Single Category Views in External Notes Databases)

Have you ever had Notes application A that had related data in Notes application B where you wanted to show the application B data when you opened a document in application A? I have, and I found there are lots of ways to combine data like this. If it were all in one database, it would be pretty trivial to create an embedded view and show the single category that relates to the open document. It gets complicated when you want to leave the data in its source database rather than copying it to where you want it, and feels almost impossible if you then introduce multiple environments (eg. development, QA and production).

Composite Applications, introduced in Notes 8, were one way to accomplish this, but they never really caught on, due in part to their own deployment issues. XPages is the latest silver bullet that can be used to slay this beast. But what if you don’t have Notes 8.5.1 and aren’t going to move to it in the near future? Or you don’t have the time to focus on learning XPages right now?

There is a way to make these related applications work in a configurable way in the Notes 6 and 7 client. This is not original thinking. See Nathan Freeman’s blog (ntf.gbs.com) for the Sesame Street and Area 51 demos for the earlier documentation. I just thought it was worth bringing up again.

The entire solution is based on three features of Domino Designer and Notes that haven’t gotten a lot of attention: computable frameset contents, embedded editors and forms that open in framesets. Nathan’s Sesame Street demo showed how you could embed a fully functional frameset on a form. That was great, unless you only wanted a single category of a view. That problem was solved in the Area 51 demo.

For this demo, I have an application catalog. Not *the* application catalog that each Domino server has, but one that adds on to that catalog to add a description, an application owner and technical support staff. Rather than copying everything from the system catalog, we’ll reference the catalog from within our application. I’ll refer to the application catalog as the “source” database and the system catalog as the “target” database. The source database documents will contain the replica ID of the application database that will be our lookup key for the target database.

Target Database
1. Create a form in the system catalog with an embedded view on it, with the view being one categorized by replica ID. You can create a view specific to your needs or use one that is already in the system catalog.

2. Set the form so that it does not appear on the Create menu.

3. On the embedded view, the Show Single Category property should be set to @Word(@Environment(“Catalog_Ref”);”~” ;4). The environment variable will be set by the source application and will be explained in the next section.

4. Save the form with the name OneReplica.

This must be created in the target database so that you can choose the “Current Database” option as the source of the embedded view. Because there is not a way to programmatically select the source of an embedded view, this ensures the proper view will always open, whether you are looking at your Dev, QA or Production system catalog.

Source Database
This database should already have a form in it for the application items that you want in addition to what is in the system catalog. To make this work, you need a field on the form for the replica ID of the application, and the value should be in the formula format (12345678:87654321), unless you created a new view in the target database that reformats the replica ID. You should also have a view that lists the application documents.

There are two elements that need to be added in the source database and one change to the application form you already have. The new elements are a frameset that will display the form from the target database, and a wrapper form that is set to display in a frame of the new frameset. On the application form, you need to add an embedded editor that will display the wrapper form.

1. Create a frameset with three frames: two side-by-side on top and one full width on the bottom. Set the top frames to a relative height of 1 and 50% width. The borders should be set to not be 3D and 2 pixels in width.

2. Name the top left frame NotesView and set its target to NotesDocView. Set the Content Type to Named Element and click the ‘@’ icon to enter formulas to determine the element. The kind of element should have the formula @Word(@Environment(“Catalog_Ref”);”~” ;2), the database should be the formula @Word(@Environment(“Catalog_Ref”);”~” ;1) and the element name should be the formula @Word(@Environment(“Catalog_Ref”);”~” ;3). The kind and name of the element could actually be constants, but it is a better practice to compute them in case they need to change later or you want to use this frameset for more than one purpose.

3. Name the top right frame NotesDocView.

4. Name the bottom frame InvisibleMan. The height of InvisibleMan should be 0 pixels, Scrolling should be Off and Allow Resizing should be No. We don’t want to see this frame; it is used only as the target for the form we want to open in a frameset.

5. Save the frameset as CatalogView.

6. Create a form. It should be set so that it does not appear on the Create menu. On the Launch properties, set it to open in the CatalogView frameset in the InvisibleMan frame. The only purpose of this form is to open in a frameset so the frameset can appear to be embedded on another form. This form could also set the environment variable, but we are not doing so in this example.


7. Save the form with the name Wrapper.

8. In the application documentation form (assumed to have been created previously), add an embedded editor that opens the Wrapper form. In the PostOpen of this form, you need code to set the Catalog_Ref environment variable. The variable should be in the format server!!filepath~Form~OneReplica~replicaID. The server could be computed based on the location of the source application and the filepath is probably constant across the environments, so you could probably enter it directly (remember to escape any ‘\’ in the path). The element type and name will be constant and the replica ID is the name of the field containing the replica ID. More likely, the server and filepath for the catalog would be read from a configuration document. Just keep in mind that the location of the target database (that is, both the server and file path) should not be hard coded, because that defeats the purpose of this solution: to make it possible to migrate from your dev to production environment without making programming changes as the moves are completed.

9. Save the updated form.

Once everything is saved, go to your source database and open or create a document using the application documentation form. If all goes well, you should see an embedded view that contains the documents from the system catalog for the replica ID of the database you are looking at, and a box to the right that should display the document you click in the view. See, a real embedded frameset!


If you want to prove that it is configurable via the environment variable, set the environment variable to @Implode(@MailDbName;"!!")+"~Folder~($Inbox)" and see that it opens your mail box.

Once you wrap your head around how this works, the possibilities become nearly endless. Multi-database applications can become far more informative.


Technorati: Show-n-Tell Thursday

Export AOL Address Book

I moved my in-laws from AOL to Thunderbird (they just aren't ready to use just web mail) and needed a way to get their address book out of AOL. I did some searching and found a couple of possibilities, but nothing easy and free. I'm pretty sure there isn't an option to export from the AOL client, but I found the way to do it.

Use the AOL webmail interface and you can export your address book to an LDIF file. Clean it up if you need to and then import it into Thunderbird. Simple!

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