Extract value from text arrays with LotusScript

Sample code used for extracting the value from text list using LotusScript.

code started from below :

'-----------------------------Declaration---------------------------------------'
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim money As Variant
Dim m As integer

Dim total As Integer

'---------------------------Initialisation-----------------------------------------'
Set db=session.Currentdatabase
Set view=db.Getview(ViewName)
Set doc= view.Getfirstdocument()
While Not doc Is Nothing
money=doc.Getitemvalue(FieldName)
For m=0 To UBound(money)
total = total +money(m)
Next
Set doc= view.Getnextdocument(doc)
Wend

Writing Status bar history to your local log file

For writing status bar history to our local log file :

For this we have to update NOTES.INI setting as LOGSTATUSBAR=1 to enable logging of status bar messages to local log file, log.nsf. To view the logged messages, open log.nsf database in in your system and then click on "Miscellaneous Events view". Status bar messages are appended with the "Status Message". 

For checking Status bar value open the Miscellaneous Events view, select document and check EventList field value.

Hiding attachments from notes document

Hiding attachments from notes document on web browsers :

To hide attachments from web browsers, there is an internal field named "$V2AttachmentOptions" that can be added anywhere to the form. $V2AttachmentOptions works only on Domino 4.6 servers or later. If this field has a value of "0" (a text zero), then all V2 Style file attachments will be hidden from Web clients. Notes clients will still be able to see the attachments. The field should be of the type Text and can be Editable, Computed or Computed for Display as desired. 

If an attachment still appears on web browser then use a Notes client to check the value of $V2AttachmentOptions field. Right-click the document and select the Document Properties and then the second tab, and then highlight the field in the list. The Data Type for the field should indicate Text. If it indicates Number then the field was programmatically set to a value of 0 rather than "0". When setting this field value via a formula or agent be sure that the text value of "0" is used. 

For example :

Formula: 

Field $V2AttachmentOptions := "0"

LotusScript: 

doc.~V2AttachmentOptions="0"

or

call doc.replaceitemvalue("$V2AttachmentOptions", "0")

Note: If setting this field via a formula or agent be sure that the it is set to a text value of "0".

Lotusscript code to export data from view to MS Excel

Sample code used to export documents from a view into an excel file. Tested this code in Microsoft Excel 2007 & 2010 and it is working fine.

Be sure that you define your own document field before starting export operation.

code started from below:-

      ‘------------------------Declaration---------------------------‘
      Dim session As New NotesSession

      Dim db As NotesDatabase

      Dim excel As Variant

      Dim worksheet As Variant

      Dim grpname As Variant

      Dim i As Integer

      Dim cell As Variant

      Dim xlSheet As Variant

      Dim groupmbrsname As String

      Dim doc As NotesDocument

      Dim view As NotesView

      Dim rowNum As Integer

      Dim cnt As Integer

      Dim wrapHeight As Integer

     

     

      Set excel = OpenMSExcel("")

     

      ‘------------Used for naming Excel Worksheet-----------------‘

      Set xlSheet = excel.Workbooks(1).Worksheets(1)

      xlSheet.Activate

      xlSheet.name="Group & Members Checklist"

     

      cnt=0

      ‘------------Used for naming the Excel Header-----------------‘

      Set worksheet = excel.Application.Workbooks(1).Sheets(1)

      Set cell = worksheet.Range("A1")

      cell.FormulaR1C1 = "Group"

      Set cell = worksheet.Range("B1")

      cell.FormulaR1C1 = "Members"

      rowNum = 2

     

      ‘---------------------Initialisation--------------------------‘

      Set db = session.CurrentDatabase

      Set view = db.GetView("ViewName")

      Set doc=view.Getfirstdocument()

      While Not doc Is Nothing

            cnt=0

            groupmbrsname=""

            grpname =doc.Members

            For i = 0 To UBound(grpname)

                  If(groupmbrsname="") Then

                        groupmbrsname= grpname (i)

                  Else

                        groupmbrsname=groupmbrsname+", "+ grpname (i)

                  End If

                  cnt=cnt+1

            Next

            Set cell = worksheet.Range("A" & CStr(rowNum))

            cell.FormulaR1C1 = doc.ListName

            Set cell = worksheet.Range("B" & CStr(rowNum))

            cell.FormulaR1C1 = groupmbrsname

            If(cell.FormulaR1C1<>"") Then

                  wrapHeight=16*cnt

      '---------------Used for automatic Wrap Text------------------'

                  Cell.RowHeight = wrapHeight  

                  cell.WrapText=True

            Else

      '--------------Used to fill cell color------------------------'

                  cell.Interior.ColorIndex = 09 

            End If

            rowNum = rowNum + 1

            Set doc = view.Getnextdocument(doc)

      Wend

     

      ‘--------- Used for providing Excel Header Style------------‘

      worksheet.Rows("1:1").RowHeight = 20.5

      Set cell = worksheet.Range("A1:B1")

      cell.WrapText = True

      cell.Font.FontStyle = "Bold"

      worksheet.Rows("1:1").Font.Name = "Times New Roman"

      worksheet.Rows("1:1").Font.Size = 15 

      cell.Interior.ColorIndex = 51

      worksheet.Rows("1:1").HorizontalAlignment = -4108

      worksheet.Columns("A:A").ColumnWidth = 30

      worksheet.Columns("B:B").ColumnWidth = 58

      excel.Visible = True   

End Sub

‘----------- Function used for creating excel object --------------------‘

Function OpenMSExcel(fileName As String) As Variant

      Dim msExcel As Variant

      Dim doc As Variant

     

      On Error GoTo CreateExcelObject

      Set msExcel = GetObject("Excel.Application")

CreatDone:

      msExcel.Visible = False

      If fileName = "" Then

            Call msExcel.Workbooks.Add

      Else

            Call msExcel.Workbooks.Open(fileName)

      End If

      Set OpenMSExcel = msExcel

      Exit Function

CreateExcelObject:

      Err = 0

      Set msExcel = CreateObject("Excel.Application")

      Resume CreatDone

End Function