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

4 tanker om "Excel integration"

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *

*