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:
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.
This was first published in August 2006