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. 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