A while back, I wrote a LotusScript agent to export data out of a Notes' view into Excel from within the Notes client
(see the code
here).
Just recently, I had the requirement to do the same thing, but from a web browser, no Notes client allowed. Some
digging around the Net resulted in an interesting way of doing it. It uses what Microsoft calls a
Web Query File. It is essentially a text file containing, at a minimum, the URL of the target data. In my case, the target was
a hidden view with very little formatting (some columns were sorted, that's about it). I attached the web query
file to a document, then created a link on a page that referenced the document with the file. When the link was clicked,
Internet Explorer opened Excel and the data was transferred into Excel. The best thing was that it happened pretty quickly...
the worst thing is that it seems to be limited to 1200 rows!!! D'OH!!! I tried every trick I could in the URL
for displaying views in Domino (&Count=9999, etc.), but that didn't work. I tried running a web query from within
Excel to my Domino view, but again, only 1200 rows were imported. I tried this using Excel 97 and Excel XP... same result.
I am not sure if this is a Domino or Excel limitation. Oh well... time for another solution.
I am now back to an Agent kicked off from a link on the page using the ?OpenAgent command. This agent is different
from the one I created above in that it formats the data into an HTML table for export to Excel. It runs pretty quickly
on smaller amounts of data. In the example here, the Agent runs up against Month To Date style views. The views
will only contain one months worth of data at any given time (and no, I am not using @Today in my view selection formulas).
Here is the code...
Sub Initialize
%REM
What this agent does...
This agent is triggerred from the ?OpenAgent URL command of each of the "MTD report" pages.
It creates a new Excel spreadsheet, filled with data from a specified Notes view, within IE by setting the
Content-Type header. The user can then save the Excel spreadsheet and manipulate the data at a later time.
Inputs...
View Name - passed in through the Query_String CGI variable, accessed by the DocumentContext
%END REM
On Error Goto errhandler
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim nvn As NotesViewNavigator
Dim nve As NotesViewEntry
Dim webDoc As NotesDocument
Dim colCount As Integer
Dim i As Integer
Dim viewName As String
Set db = session.CurrentDatabase
Set webDoc = session.DocumentContext
viewName = Strrightback(webDoc.Query_String(0), "view=")
Set view = db.GetView(viewName & "ToExcel")
Set nvn = view.CreateViewNav()
Set nve = nvn.GetFirst
Print |Content-Type:application/vnd.ms-excel|
Print |<table border="1">|
Print |<tr>|
Forall c In view.Columns
Print|<td width="180"><b>| & c.Title & |</b></td>|
End Forall
Print |</tr>|
colCount = view.ColumnCount - 1
Print |<tr><td colspan="| & colCount & |"> </td>|
While Not(nve Is Nothing)
Print |<tr>|
For i = 0 To colCount
Print |<td>| & nve.ColumnValues(i) & |</td>|
Next
Print |</tr>|
Set nve = nvn.GetNext(nve)
Wend
Print |</table>|
Exit Sub
errhandler:
Print "<br><font face=""Verdana"" color=""#000080"" size=""1"">An error occured in the Initialize object while
searching for your record. Please contact your local QA Administrator and give them the following information... <br>Error"
& Str(Err) & ": " & Error$ & ". Error occurred at line " & Erl() & "</font>"
Exit Sub
End Sub
