I think that the following could be used in earlier
versions of Excel as it turns any view straight into
an HTML table. Since Excel will open and convert
these files directly it gives one a solution where
views can be exported as HTML and then emailed
around. The other advantage of this is that you could, for example, email the
file to somebody
using Lotus 123, as I believe that this package also
lets you open such tables.
Note that this code intentionally uses the
notesDocument class (as opposed to
notesViewEntry) so that you can use it on either r4
or r5. If however, you are using r5 then I would
recommend updating to using the entry class to
help performance.
Finally, the <br /> delimiter causes Excel (at least
on 2000) to split one HTML table cell into many
Excel cells, although the overall spreadsheet still
lines up correctly. You may remove this delimiter
to keep multiple items in one cell in Excel or,
alternatively, use a different delimiter.
Sub viewToHTML (db As notesDatabase, viewname As String, filename As String)
Const myFunction = "viewToHTML"
On Error Goto errorHandler
Dim s As New notesSession
Dim view As notesView
Dim doc As notesDocument
Dim fileNum As Integer
Dim counter As Integer
' Specify the default settings
Set view = db.GetView (viewname)
Set doc = view.GetFirstDocument
fileNum% = Freefile ()
Open fileName For Output As fileNum%
' Export the HTML header information to the file
Print #fileNum%, |<html>|
Print #fileNum%, Tab (2) ; |<head>|
Print #fileNum%, Tab (4) ; |<title>| & viewname & | on | & Today () &
|</title>|
Print #fileNum%, Tab (2) ; |</head>|
Print #fileNum%,
' Export the HTML body
Print #fileNum%, Tab (2) ; |<body>|
Print #fileNum%, Tab (4) ; |<div align="center">|
Print #fileNum%, Tab (6) ; |<table cellpadding="0" cellspacing="0"
border="1">|
' Export the visible Column's titles
Print #fileNum%, Tab (8) ; |<tr>|
Forall c In view.Columns
If (Not c.isHidden) Then
Print #fileNum%, Tab (10) ; |<th>| & c.Title & |</th>|
End If
End Forall
Print #fileNum%, Tab (8) ; |</tr>|
Print #fileNum%,
' Export the information for each document in the view
While Not (doc Is Nothing)
Print #fileNum%, Tab (8) ; |<tr>|
counter% = 0
Forall values In doc.ColumnValues
' Check if the column is visible. If it is not then do not
export it
If (Not view.Columns (counter%).IsHidden) Then
Print #fileNum%, Tab (10) ; |<td valign="top">|
If Isscalar (values) Then
Print #fileNum%, Tab (12) ; values
Else
Forall v In values
Print #fileNum%, Tab (12) ; v & |<br />|
End Forall
End If
Print #fileNum%, Tab (10) ; |</td>|
End If
counter% = counter% + 1
End Forall
Print #fileNum%, Tab (8) ; |</tr>|
Print #fileNum%,
Set doc = view.GetNextDocument (doc)
Wend
' Complete the table and the rest of the HTML
Print #fileNum%, Tab (6) ; |</table>|
Print #fileNum%, Tab (4) ; |</div>|
Print #fileNum%, Tab (2) ; |</body>|
Print #fileNum%, |</html>|
Hiya,
I think that the following could be used in earlier versions of Excel as it turns any view straight into an HTML table. Since Excel will open and convert these files directly it gives one a solution where views can be exported as HTML and then emailed around. The other advantage of this is that you could, for example, email the file to somebody using Lotus 123, as I believe that this package also lets you open such tables.
Note that this code intentionally uses the notesDocument class (as opposed to notesViewEntry) so that you can use it on either r4 or r5. If however, you are using r5 then I would recommend updating to using the entry class to help performance.
Finally, the <br /> delimiter causes Excel (at least on 2000) to split one HTML table cell into many Excel cells, although the overall spreadsheet still lines up correctly. You may remove this delimiter to keep multiple items in one cell in Excel or, alternatively, use a different delimiter.
Ben.
------------------------------------------------------------
Option Declare
Sub Initialize
Dim s As New notesSession
Print "Starting"
viewToHTML s.currentDatabase, "myView", "C:\myFile.htm"
Print "Done"
End Sub
Sub viewToHTML (db As notesDatabase, viewname As String, filename As String)
Const myFunction = "viewToHTML"
On Error Goto errorHandler
Dim s As New notesSession Dim view As notesView Dim doc As notesDocument Dim fileNum As Integer Dim counter As Integer
' Specify the default settings Set view = db.GetView (viewname) Set doc = view.GetFirstDocument fileNum% = Freefile ()
Open fileName For Output As fileNum%
' Export the HTML header information to the file Print #fileNum%, |<html>| Print #fileNum%, Tab (2) ; |<head>| Print #fileNum%, Tab (4) ; |<title>| & viewname & | on | & Today () & |</title>| Print #fileNum%, Tab (2) ; |</head>|
Print #fileNum%,
' Export the HTML body Print #fileNum%, Tab (2) ; |<body>| Print #fileNum%, Tab (4) ; |<div align="center">| Print #fileNum%, Tab (6) ; |<table cellpadding="0" cellspacing="0" border="1">|
' Export the visible Column's titles Print #fileNum%, Tab (8) ; |<tr>| Forall c In view.Columns If (Not c.isHidden) Then Print #fileNum%, Tab (10) ; |<th>| & c.Title & |</th>| End If End Forall Print #fileNum%, Tab (8) ; |</tr>|
Print #fileNum%,
' Export the information for each document in the view While Not (doc Is Nothing) Print #fileNum%, Tab (8) ; |<tr>| counter% = 0 Forall values In doc.ColumnValues ' Check if the column is visible. If it is not then do not export it If (Not view.Columns (counter%).IsHidden) Then Print #fileNum%, Tab (10) ; |<td valign="top">| If Isscalar (values) Then Print #fileNum%, Tab (12) ; values Else Forall v In values Print #fileNum%, Tab (12) ; v & |<br />| End Forall End If Print #fileNum%, Tab (10) ; |</td>| End If counter% = counter% + 1 End Forall Print #fileNum%, Tab (8) ; |</tr>| Print #fileNum%, Set doc = view.GetNextDocument (doc) Wend
' Complete the table and the rest of the HTML Print #fileNum%, Tab (6) ; |</table>| Print #fileNum%, Tab (4) ; |</div>| Print #fileNum%, Tab (2) ; |</body>| Print #fileNum%, |</html>|
' Close the HTML file Close fileNum%
Exit Sub
errorHandler: Print _ |[| & s.currentAgent.name & _ |-| & myFunction & _ |]| & | Error: | & Error$ & | (| & Cstr (Err) & |) at line | & Cstr (Erl)
Close fileNum% Kill fileName
Error Err, Error$ End Sub