Live text and Widgets

LiveText is just awesome. If you don’t know what I’am talking about, watch the IBM Demo

Getting started:
* Articles and Step-by-step instructions can be found in the blog from Alan Lepofsky.
* The tutorial is great – in 3-4 hours a guided tour will teach you all you need to know… 🙂

Learning Regular Expressions:
* Tutorial and samples -> http://www.regular-expressions.info/
* You can test your expression here -> http://regex.powertoy.org/
* or buy a regular expression builder tool -> http://www.regexbuddy.com/

More ressources:
* DominoWiki.com
* IBM information center

Word Integration

In this article, I’ll show how you can launch word from Lotus Notes and transfer data to/from bookmarks in the word document

Create a new Word document
        Set obj = CreateObject(“Word.Application”)
        Call obj.documents.add()
        set w = obj.ActiveWindow

Create a Word document from a file
        Set obj = CreateObject(“Word.Application”)
        Call obj.documents.open( filename )
        Set w = obj.ActiveDocument

To save the changes you can use
        Call w.save()
or
        Call w.saveAs( filename)

Write data from Notes to a bookmark in the word document
        ‘Bookmark insert and recreate
        Dim bmRange As Variant
        Set bmRange = w.Bookmarks( nameOfBookmark ).Range
        bmRange.Text = Cstr( value ) ‘insert new text
        w.Bookmarks.Add position, bmRange ‘recreate the bookmark

or read data from a bookmark
        value = w.Bookmarks( nameOfBookmark ).Range.Text

More ressources

Checkout the Attachmate script library in my Design Template database. In this database you have full integration to Word and Excel – like a light version of Intregra or Swing ..
How to use a single VBA procedure to read or write both custom and built-in Document Properties
If your are developing VBA in Lotus Notes – you do need the Microsoft Constant Database

 

Excel integration

From Lotus Script you can use CreateObject( “Excel.Application”) to get access to all the Excel classes. Now it is easy to create a new Excel sheet and fill it with data from Lotus Notes. MSDN: Excel Object Reference

Sample 1: Using a template file
        Set excel = CreateObject(“Excel.Application”)
        Set book = excel.Workbooks.open( filename )
        Set sheet = excel.Worksheets( 1 )
        excel.Visible = True

Sample 2: Creating a new blank sheet
        Set excel = CreateObject(“Excel.Application”)
        Set book = excel.Workbooks.Add()
        Set sheet = excel.Worksheets( 1 )
        excel.Visible = True

To read a value from Excel, use
        value = sheet.range( “A2” ).value
or
        value = sheet.Cells( 1, 2 ).value ‘col, row

To write data to a cell, use:
        sheet.range( “A2” ).value = value
or
        sheet.Cells( 1, 2 ).value = value ‘col, row

Useful functions to convert between a A1 style name and a row,column index:

‘Converts a coordinate (1,2) to a named reference (“A2”)
Function getCellIndex( col As Integer, row As Integer ) As String
        Const AZ = “ABCDEFGHIJKLMNOPQRSTUVWXYZ”
        Dim index As String
       
        Dim l As Integer
        Dim d As Integer
        Dim r As Integer
       
        l = Len( AZ )
        d = col l
        r = col Mod l
       
        If r = 0 Then r = l : d = d-1
       
        If d > 0 Then
                index = Mid(AZ,d,1 )
        End If
       
        index = index & Mid( AZ,r,1 )
        index = index & Cstr( row )
       
        getCellIndex = index
End Function

‘Converts a named reference (“A2”) to a coordinate in the R1C1 style (“R2C1”)
Function r1c1( cell As String ) As String
        Dim row As String
        Dim column As String
       
        Dim n As String
        Dim char As String
       
        If Instr( cell, “!” ) > 0 Then cell = Strright( cell, “!” )
       
        char = Mid( cell, Len(cell)-Len( n ), 1 )
        While Isnumeric( char )
                n = n & char
                char = Mid( cell, Len(cell)-Len( n ), 1 )
        Wend
        If n = “” Then n = “1”
       
        column = Ucase( Left( cell, Len( cell )-Len( n )))
        row = n
       
        If Len( column ) > 1 Then
                column = Cstr( ( Asc( Left( column,1 ))-Asc( “A” ) +1 ) * 28 + (Asc( Right( column,1 ))-Asc( “A” ) +1))
        Else
                column = Cstr( Asc( column )-Asc( “A” ) +1)
        End If
       
        r1c1 = “R” & row & “C” & column
End Function        

Other resources

How to copy/paste a ReplicaID

You can see the ReplicaID on the Info tab in the database properties – but you can’t copy from here. Instead you can open the database and double click on the select button in the addressbar (see screenshot). now you have the complete notes url to the database – and you can easily copy/paste the replicaid (or document unid or view id) from here.
Another way is to open the Document properties box – on the meta tab (marked with an +) you wil find the complete notes url in the ‘identifier’ field

Newline codes in Notes documents

If you write this value to a Notes document:
“Hello” & Chr( 10 ) & “World”
Notes will automatically convert the Chr( 10 ) to a full 2 bytes newline: Chr( 13 ) & Chr( 10 )

Don’t know if this is good or bad – but it took me some time to figure out why the strings on my document was changed…
Sample code:


Dim session As New NotesSession
Dim doc As NotesDocument
Set doc = session.CurrentDatabase.CreateDocument
Dim s As String
s = “Hello” & Chr( 10 ) & “World” ‘Use Chr( 13 ) & Chr( 10 ) instead
doc.string = “Hello” & Chr( 10 ) & “World”
If doc.string( 0 )
s Then
Print “Not has auto converted Chr( 10 ) to Chr( 13 ) & Chr( 10 )”
End If