This Web Query Save agent takes a Microsoft Word or Microsoft Excel file and converts it to HTML.
It then creates a new Lotus Notes document and attaches the converted HTML files and the original Microsoft Office file to respective rich-text fields.
This allows Lotus Notes users to view an HTML version of an attachment via the Web, without having to download and open Microsoft Word or Microsoft Excel.
To implement this, only two forms are required. The first is a form with a file upload control that calls the attached WQS agent. The second is a form called "Document" with four fields: HTMLFilename (text), PrintFilename (text), PrintFile (richtext), HTMLFiles (richtext).
Your Lotus Domino server must be running on a Win32 platform with Microsoft Office 2003 installed, so that the Web Query Save agent can instantiate Microsoft Word or Microsoft Excel through the "CreateObject" method.
Sub Initialize
userErrorMessage$ = "" 'string to send in case of error
On Error Goto Uh_Oh
'Open the new agent log
Dim agentLog As NotesLog
Set agentLog = New notesLog("Agent log")
Call agentLog.OpenAgentLog
'get the current document
Dim session As New NotesSession
Dim db As NotesDatabase
Dim webDoc As NotesDocument
Dim v2File As NotesItem
Dim fileName As String
Dim OFF As Variant
Dim NotesPath As String
Dim TempPath As String
Set db = session.CurrentDatabase
Set webDoc = session.DocumentContext
'Get server name as the NotesDatabase
must have the server argument
Dim server As String
Dim agent As Variant
Set agent = session.CurrentAgent
server = agent.ServerName
'Set up Temp directory for working with OFF Files
NotesPath = session.GetEnvironmentString
("Directory", True)
TempPath = NotesPath & "TempOFF"
On Error Goto TempDirError
attr% = Getfileattr(TempPath)
If attr% <> 16 Then
Mkdir TempPath
End If
FinishPathSetup: 'used as the
resume point after an error has occured
If Not webdoc.HasItem( "$File" ) Then Goto NoFileError
'Get handle to OFF file from the
uploaded document and check for .doc or .xls
Set v2File = webdoc.GetFirstItem ( "$File" )
fileName = v2File.Values(0)
fileExt = Ucase$(Right$(fileName,3))
If fileExt = "DOC" Or fileExt = "XLS"
Then Else Goto NotOFFError
'Create the temporary working directory
to hold uploaded office file
OFFDir$=TempPath & "OFFDir" &
webdoc.UniversalID
Mkdir OFFDir$
'Create the temporary working directory
to hold html files
htmDir$=TempPath & "htmDir" &
webdoc.UniversalID
Mkdir htmDir$
'Extract OFF file
Set OFFFile = webdoc.GetAttachment ( fileName )
UFileName$ = OFFDir$ & "" & filename
'--Save the file on the server
Call OFFFile.ExtractFile ( UFileName$ )
'Set Up OFF object for processing
using different methods for DOC vs XLS
Call agentLog.logAction("begin office processing")
If fileExt = "DOC" Then 'Format for Word files
Set OFF = CreateObject("Word.Application")
Call agentLog.logAction("create word object")
OFF.Visible = False
OFF.Documents.Open UFileName$
Call agentLog.logAction("open" & UFileName$)
OFF.Documents(1).Activate
OFF.ActiveDocument.WebOptions.
OrganizeInFolder = False
OFF.ActiveDocument.SaveAs htmDir$
& "" & filename & ".htm", 10 '10
is constant for HTML Filtered
OFF.ActiveDocument.Close 0
OFF.Quit 0
Call agentLog.logAction("saved html")
Elseif fileExt="XLS" Then 'Format for Excel files
Set OFF = CreateObject("Excel.Application")
OFF.DisplayAlerts = False
Call agentLog.logAction("create Excel object")
OFF.Visible = False
OFF.Workbooks.Open UFileName$
Call agentLog.logAction("open" & UFileName$)
OFF.Workbooks(1).Activate
OFF.ActiveWorkbook.WebOptions.
OrganizeInFolder = False
OFF.ActiveWorkbook.SaveAs htmDir$
& "" & filename & ".htm",
44 ' 44 is constant for HTML
OFF.ActiveWorkbook.Close 0
Call agentLog.logAction("saved html")
OFF.Quit
End If
'Make New Document to hold all the files
Dim newDoc As NotesDocument
Dim OFFRtitem As NotesRichTextItem
Dim htmRtitem As NotesRichTextItem
Dim OFFObject As NotesEmbeddedObject
Dim htmObject As NotesEmbeddedObject
Set newDoc = New NotesDocument( db )
Set OFFRtitem =
New NotesRichTextItem( newDoc, "PrintFile" )
Set htmRtitem =
New NotesRichTextItem( newDoc, "HTMLFiles" )
'attach office doc
Set OFFObject = OFFRtitem.EmbedObject
( EMBED_ATTACHMENT, "", UFileName$)
'Loop through each of the html files and attach them
'Directory of htm files
htmFiles$ = htmDir$ & "*.*"
htmFile$=Dir$(htmFiles$, 0)
If htmFile$ = "" Then Goto done
Do While htmFile$ <> ""
htmFileName$ = htmDir$ & "" & htmFile$
'attach html files
Set htmObject = htmRtitem.EmbedObject
( EMBED_ATTACHMENT, "", htmFileName$)
Kill htmFileName$ ' remove from filesys after attaching
htmFile$ = Dir$() 'increment to the next file
Loop
'save the new document
newDoc.Form = "Document"
newDoc.HTMLFilename = filename & ".htm"
newDoc.PrintFilename = filename
Call newDoc.Save( True, True )
'Clean up
Set OFF = Nothing
Kill UFileName$ 'delete the office file
Rmdir(OFFDir$) 'delete the temp htm directory
Rmdir(htmDir$) 'delete the temp OFF directory
Goto done
Uh_Oh: 'there was an error so attempt to
send an e-mail to the administrator
Print ""
Print "<h3>There was an unknown error
uploading the file.
Please contact the system administrator.</h3>"
done: 'there was no error
webpath$=Strleft(webdoc.Path_Info(0),".nsf") & ".nsf"
Print "<FONT FACE=Arial><h3>
The upload process has been completed. </h3>"
Print "<FONT FACE=Arial><a href=" & webpath$
& ">Continue</a></FONT><br><br>"
'make a link back to a specific view here
completelyDone: 'we're finally done
Goto megaDone
TempDirError:
Mkdir TempPath
Resume FinishPathSetup
NotOFFError:
Print "<FONT FACE=Arial>
<h3>Only "".DOC"" or "".XLS"" files
can be uploaded.
Click the back button on your browser and select
another file.</h3></FONT>"
Goto megaDone
NoFileError:
Print "<FONT FACE=Arial><h3>
No file was selected for uploading.
Click the back button on your browser
and select a file.</h3></FONT>"
Goto megaDone
megaDone:
Set OFF = Nothing
Call agentLog.close()
End Sub
As a side note, there are many more interesting things you can do with Microsoft Office documents through automation, including converting PowerPoint files (which this agent unfortunately does not cover). For more details, go to Microsoft Word -> Visual Basic Editor -> Help for reference.
Do you have comments on this tip? Let us know.
Related information from SearchDomino.com:
Tip: Creating PDF documents from Lotus Notes
Tip: Exporting email from Lotus Notes to .EML messages
Reference Center: Tips and resources on Notes/Domino agents
This tip was submitted to the SearchDomino.com tip library by member Tim Wynn. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.
