Agent to Take a View and Export its Column & Row Data to Text File & Attach the Text File to a Page.

Mindwatering Incorporated

Author: Tripp W Black

Created: 03/16/2001 at 10:43 PM


Notes Developer Tips
Agents, LotusScript

Sub Initialize

' This agent writes a comma-separated variable text file as per
' the "PhoneBookforExp" view. This text file is then attached
' into the "phone.csv" doc, so that users can download the
' directory and load it into MS Excel.

Dim tempFile As String
' tempFile points to where we write phone.csv.
tempFile = "d:\notes\data\phone.csv"

' Write the .csv "export" of phone directory
outFileNum% = Freefile
Open tempFile For Output As outFileNum%
Dim db As New NotesDatabase("", "phone\phone.nsf") 'MUST BE PHONE DIR
Dim view As NotesView
Dim doc As NotesDocument
Set view = db.GetView( "PhoneBookforExp" )

Dim colCount As Integer
colCount = 0
Dim colVal As Variant
Dim outBuf As String
Dim printBuf As String

' write column headers
Forall c In view.Columns
If Not c.IsHidden Then outBuf = outBuf & """" & c.Title & ""","
colCount = colCount + 1
End Forall
printBuf = Left$(outBuf, Len(outBuf) -1)
Print #outFileNum%, printBuf
outBuf = ""
colCount = 0

' write view column data
Set doc = view.GetFirstDocument
While Not ( doc Is Nothing )
Forall c In view.Columns
If Not c.IsHidden Then
colVal = doc.ColumnValues(colCount)
outBuf = outBuf & """" & colVal & ""","
End If
colCount = colCount + 1
End Forall
printBuf = Left$(outBuf, Len(outBuf) -1)
Print #outFileNum%, printBuf
outBuf = ""
Set doc = view.GetNextDocument( doc )
colCount = 0
Close outFileNum%
Print "Wrote output file: " & tempFile

' Update the attachment in this database
Dim session As New NotesSession
Set db2 = session.CurrentDatabase

Dim view2 As NotesView
Dim doc2 As NotesDocument

Set view2 = db2.GetView( "Attachments\Documents" )
Set doc2 = view2.GetDocumentByKey("phone.csv", False)
If doc2 Is Nothing Then
Print "Doc phone.csv not found"
Call doc2.Remove(True)
End If

Set doc2 = db2.CreateDocument
doc2.attComments="Spreadsheet Phone Book - AUTOCREATED BY AGENT"
doc2.FriendlyURLAddress="Click here for the Phone Book"
Dim rtItem As NotesRichTextItem
Set rtitem = New NotesRichTextItem( doc2, "DominoFileAttach")
Call rtitem.EmbedObject(EMBED_ATTACHMENT, "", tempFile)
Dim success As Variant
success = doc2.ComputeWithForm( False, False )
If success Then
Call doc2.Save( True, True )
Print "Updated attachment file for web download"
Print "Failed to ComputeWithForm, did not save update file"
Exit Sub
End If

' Update the Download page
Set view = db2.GetView( "Publish\Docs" )
Set doc = view.GetDocumentByKey("Download", False)
If doc Is Nothing Then
Print "Doc ""Download"" not found"
doc.PhoneDirLastUpdated="Last Updated: " & Str(doc2.Lastmodified)
Call doc.Save(True, True)
End If

End Sub

previous page