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