Avatar of gliese581

asked on 

LotusScript to export view data

When I run the Action below, I get the following error msg: File Not Open.

The code comes from the following DominoSearch Tip titled, "How to export Lotus Notes Views to Microsoft Excel databse".
Sub Initialize
'I placed all of the code in a single sub (normally I split it all out... but for this demo purpose, I just put all code in one sub)
	Dim doc As NotesDocument
	Dim uidoc As NotesUIDocument
	Dim tmpdir As String
	Dim uiview As NotesUIView
	Dim view As NotesView
	Dim dc As NotesDocumentCollection
	Dim ret As Integer
	Dim session As NotesSession
	Dim db As NotesDatabase
	Dim workspace As NotesUIWorkspace
	Dim tmpdatafilename As String
	Dim tmpdatafilenumber As Integer
	Dim memodoc As NotesDocument
	Dim rtitem As NotesRichTextItem
	Dim object As NotesEmbeddedObject
	Set session = New NotesSession
	Set workspace = New NotesUIWorkspace
	Set db = session.CurrentDatabase
	tmpdir = session.GetEnvironmentString("Directory", True)
	tmpdatafilenumber% = Freefile()
	tmpdatafilename = tmpdir & "HRReport.xls"
'Open a file for output Open tmpdatafilename  For Output As tmpdatafilenumber
	Set uiview = workspace.CurrentView
	Set view = uiview.View
'Print to the file some basic CSS for easy formatting later if the users want a change to the default.
	Print # tmpdatafilenumber%, |
    <TITLE>Table example</TITLE>
    <STYLE type="text/css">
TABLE  { background: #ffffff; border: solid black;
               empty-cells: hide }
 TD     { border: solid black;
border-left: none;
border-right: none;
TD.top     { border: solid black;
border-left: none;
border-right: none;
background: #C0C0C0;
font-weight: bold;
font-size: 11px;
text-align: center;
TD.viewname     { border: solid black;
border-left: none;
border-right: none;
border-bottom: none;
background: #C0C0C0;
font-weight: bold;
font-size: 16px;
text-align: left;
 TD.salary     { border: solid black;
border-left: none;
border-right: none;
background: #00FFFF;
'Heart of the code....
'Print an HTML Table and then construct the table from the column contents of the current view.
	Print # tmpdatafilenumber%, |<table>|
'Include the view name in the top of the spreadsheet
	Print # tmpdatafilenumber%, |<tr>|
	Print # tmpdatafilenumber%, |<td class="viewname" colspan="| & Cstr((Ubound(view.Columns) + 1)) & |">|
	Print # tmpdatafilenumber%, view.Name
	Print # tmpdatafilenumber%, |</td>| 
	Print # tmpdatafilenumber%, |</tr>|
'Start a row of headers (couldn't get TH CSS to work, so I created my own class called top)
	Print # tmpdatafilenumber%, |<tr>|
	Forall vc In view.Columns
		Print # tmpdatafilenumber%, |<td class="top">|
		Print # tmpdatafilenumber%, vc.title
		Print # tmpdatafilenumber%, |</td>|
	End Forall
	Print # tmpdatafilenumber%, |</tr>|
'Now loop though all of the documents in the view and create a HTML for each one.
'For each column, put in the cell tag.
	Set doc = view.GetFirstDocument
	Do While Not doc Is Nothing
		Print # tmpdatafilenumber%, |<tr>|
		Forall c In doc.ColumnValues
			Print # tmpdatafilenumber%, |<td>|
			If Isarray(c) Then  'Make sure you work with a multivalued field.
				For a = 0 To Ubound(c)
					If a = 0 Then
						Print # tmpdatafilenumber%, c(a)
						Print # tmpdatafilenumber%, "<br>" & c(a)
					End If
				Print # tmpdatafilenumber%, |</td>|
				Print # tmpdatafilenumber%, c
			End If
			Print # tmpdatafilenumber%, |</td>|
		End Forall
		Print # tmpdatafilenumber%, |</tr>|
		Set doc = view.GetNextDocument(doc)
'Now that you have all of your data, end the table and html tags
	Print # tmpdatafilenumber%, |</table>|
	Print # tmpdatafilenumber%, |</body>|
	Print # tmpdatafilenumber%, |</html>|
	Close tmpdatafilenumber%
'Now you can either prompt them for where the report is on their hard drive, or you can email it To them
'If like email....
	Set memodoc = db.CreateDocument
	memodoc.Form = "Memo"
	memodoc.Subject = "Exported View Report"
	Set rtitem = New NotesRichTextItem(memodoc, "Body")
	Call rtitem.AppendText(memodoc.Subject(0))
	Call rtitem.AppendText(Chr$(10) & Chr$(10))
	Call rtitem.AppendText("Open the attached file in Excel to view the report.")
	Call rtitem.AppendText(Chr$(10) & Chr$(10))
	Set object = rtitem.EmbedObject _
	( EMBED_ATTACHMENT, "", tmpdatafilename)
	memodoc.SendTo = session.UserName
	Call memodoc.Send(False)
	Kill tmpdatafilename  ' Delete the file from the users hard drive if you email the report.
	Messagebox "Please check your inbox for the report", 0 + 64, "Report Complete"
End Sub

Open in new window

Lotus IBM

Avatar of undefined
Last Comment

8/22/2022 - Mon