Collect data with LotusScript and output to Excel
Use LotusScript to collect data from a view or from a database and output it to MS Excel.
Use LotusScript to collect data from a view or from a database and output it to MS Excel. Users can create graph/charts or perform other math calculations, then save or print the report in MS Excel format.
Sub Click(Source As Button) Print "Please Wait.....Running MSExcel report" Dim session As New NotesSession Dim doc As NotesDocument Dim doc2 As NotesDocument Dim rtitem As NotesRichTextItem Dim object As NotesEmbeddedObject Dim handle As Variant Dim db As NotesDatabase Dim view As NotesView Dim counter As Integer counter3 = 0 counter4 = 0 counter5 = 0 counter6 = 0 counter7 = 0 yescounter3 = 0 yescounter4 = 0 yescounter5 = 0 yescounter6 = 0 yescounter7 = 0 Set db = session.CurrentDatabase Set view = db.GetView( "StateReport" ) Set doc2 = view.GetFirstDocument ' Forall c In view.Columns ' Messagebox( c.Position & " " & c.Title ) ' End Forall Columnname3 =view.Columns(3).Title Columnname4 =view.Columns(4).Title Columnname5 =view.Columns(5).Title Columnname6 =view.Columns(6).Title Columnname7 =view.Columns(7).Title While Not ( doc2 Is Nothing ) vtitle5 = doc2.ColumnValues( 3 ) vNSR = doc2.ColumnValues( 4 ) vPSD = doc2.ColumnValues( 5 ) vUtil = doc2.ColumnValues( 6 ) vTier2 = doc2.ColumnValues( 7 ) If vtitle5 = "No" Then counter3 = counter3 + 1 Else If vtitle5 = "Yes" Then yescounter3 = yescounter3 + 1 End If End If If vNSR = "No" Then counter4 = counter4 + 1 Else If vNSR = "Yes" Then yescounter4 = yescounter4 + 1 End If End If If vPSD = "No" Then counter5 = counter5 + 1 Else If vPSD = "Yes" Then yescounter5 = yescounter5 + 1 End If End If If vUtil = "No" Then counter6 = counter6 + 1 Else If vUtil = "Yes" Then yescounter6 = yescounter6 + 1 End If End If If vTier2 = "No" Then counter7 = counter7 + 1 Else If vTier2 = "Yes" Then yescounter7 = yescounter7 + 1 End If End If Set doc2 = view.GetNextDocument( doc2 ) Wend Set doc = New NotesDocument( session.CurrentDatabase ) Set rtitem = New NotesRichTextItem( doc, "Body" ) Set object = rtitem.EmbedObject _ ( EMBED_OBJECT, "Microsoft Excel Worksheet", "", _ "Report" ) Set handle = object.Activate( True ) handle.Application.Cells( 1,2).Value = Columnname3 handle.Application.Cells( 1,3).Value = Columnname4 handle.Application.Cells( 1,4).Value = Columnname5 handle.Application.Cells( 1,5).Value = Columnname6 handle.Application.Cells( 1,6).Value = Columnname7 handle.Application.Cells( 2,1).Value = "Yes" handle.Application.Cells( 2,2).Value = yescounter3 handle.Application.Cells( 2,3).Value = yescounter4 handle.Application.Cells( 2,4).Value = yescounter5 handle.Application.Cells( 2,5).Value = yescounter6 handle.Application.Cells( 2,6).Value = yescounter7 handle.Application.Cells( 3,1).Value = "No" handle.Application.Cells( 3,2).Value = counter3 handle.Application.Cells( 3,3).Value = counter4 handle.Application.Cells( 3,4).Value = counter5 handle.Application.Cells( 3,5).Value = counter6 handle.Application.Cells( 3,6).Value = counter7 handle.Application.Worksheets.Copy ' handle.Parent.Save ' If ( handle Is Nothing ) Then ' doc.Subject = "This object has no OLE automation interface" ' Else ' doc.Subject = "This object has an OLE automation interface" ' End If ' Call doc.Save( True, True ) Print "Done" Print " " End Sub