You are viewing this page out of context. To see it in the context it is intended please click here.
About This Page
Reply posted by Ben Newman on Wed 12 Dec 2001 in response to Keeping the boss happy
Try export straight to HTML file
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