About This Code
Brief Description:
Application developer - MS Excel format.
Contributor:
Andrew Jones
Last Modified:
17 Jun 2002
OpenNTF Disclaimer
All of the program code and information presented in the OpenNTF.org Code Bin are provided "as-is", and should be used at your own risk. OpenNTF.org make no express or implied warranty about anything in the Code Bin, and OpenNTF.org will not be responsible or liable for any damage caused by the use or misuse of anything from this site. OpenNTF.org makes no guarantees about anything. Please thoroughly test all of the knowledge and code you find here before you attempt to use them in your production environment.
Code / Description
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
Usage / Example
Comments
Posted by swaroopa s swaroopa on 01/28/2008 11:28:29 PMMS Excell format
Its easy code, but here there is a view named as "StateReport", but no view is seen in screen shorts also. How any developer is going to identify the syntax of this view. If not screen shorts then atleast syntax of above view should be provided.