Hi All,
I have a problem saving the document, importing a XL file and writing some data to word file from excel and then saving the document into Notes. I am trying to do below scenario for an Email merge application.
My scenario is like below :
1. User will opens a form where he will selects a Excel File and a word template file in 2 different fields. These fields will show the path of the files (Not attachments). Excel and Word files are predefined by the user. Excel file columns are ToList, CCList, BCCList, Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10.
Field1, Field2, Field3..... are the variable values which need to write in the word template file. ie., these variable values will replace in the word template file for each document in Excel.
2. After selecting the files by the user, he will clicks on a action button.
3. I am writing down some code under this button where it will opens the XL file and read the first record, capture the data (entire row) and then opens the word file, writes the variables(Field1 and Field2 ....) data into word file and copies this entire word file content into another forms RTF field and saves this document.
4. Everthing is working fine except variables is storing in word document and pasting the content in the RTF field. But its not saving to db. And ToList, CCList, BCCList is saving in some other document.... I need to save all the data into a single document.
I guess i am composing 2 different documents... But i am not sure how i can merge these into single document....
Actually i am writing this code in a agent and calling this when user clicks on a button.
My agent code is :
(Declarations)
Dim doc As NotesDocument
Dim ToList As String, CCList As String, BCCList As String
Dim Var1 As String,Var2 As String, Var3 As Variant,Var4 As Variant,Var5 As Variant,Var6 As Variant,Var7 As Variant,Var8 As Variant
Dim Var9 As Variant, Var10 As Variant
Dim db As NotesDatabase
Initialize
Sub Initialize
Dim workspace As New NotesUIWorkspace,xlFilename
Dim session As New NotesSession
Dim key As String
Set db = session.CurrentDatabase
Dim srchdoc, srchdoc1 As NotesDocument
Set cuidoc = workspace.CurrentDocument
cuidoc.refresh True 'to update RT fields
If Not cuidoc.editmode Then Exit Sub
Set doc = cuidoc.Document
xlFilename = doc.TextELoc
If Isempty(xlFilename) Then Exit Sub
If xlfilename(0)="" Then
Msgbox " You have to give Proper FileName",,"Message"
Exit Sub
End If
var202=Lcase(xlFilename(0))
var203=Instr(var202,".xls")-8
If var203<0 Then
Msgbox " Please enter the Proper Excel FileName",,"Message"
Exit Sub
End If
'Chk if the document exist and then migrate
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application" )
Excel.Visible = False '// Don't display the Excel window
Print "Opening " & xlFilename(0) & "..."
Excel.Workbooks.Open xlFilename(0) '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
Dim view As NotesView
Set view=Db.GetView("Mail Merge View1")
Records:
row = 1
written = 0
' Deletion for existing records to be done
Print "Start importing from Excel file..."
Do While True
With xlSheet
row = row + 1
If .Cells( row, 1 ).Value <> "" Then
ToList = .Cells( row, 1 ).Value
CCList = .Cells( row, 2 ).Value
BCCList = .Cells( row, 3 ).Value
Var1 = .Cells( row, 4 ).Value
Var2 = .Cells( row, 5 ).Value
Var3 = .Cells( row, 6 ).Value
Var4 = .Cells( row, 7 ).Value
Var5 = .Cells( row, 8 ).Value
Var6 = .Cells( row, 9 ).Value
Var7 = .Cells( row, 10 ).Value
Var8 = .Cells( row, 11 ).Value
Var9 = .Cells( row, 12 ).Value
Var10 = .Cells( row, 13 ).Value
If ToList <> "" Then Set srchdoc1 = view.GetDocumentbyKey(ToList)
Set maildoc = New NotesDocument( db )
maildoc.Form = "FrmMemo"
maildoc.SendTo = ToList
maildoc.CopyTo = CCList
maildoc.BlindCopyTo = BCCList
maildoc.Subject = doc.TextSubject(0)
maildoc.Var1 = Var1
maildoc.Var2 = Var2
maildoc.Var3 = Var3
maildoc.Var4 = Var4
maildoc.Var5 = Var5
maildoc.Var6 = Var6
maildoc.Var7 = Var7
maildoc.Var8 = Var8
maildoc.Var9 = Var9
maildoc.Var10 = Var10
maildoc.DispName = session.CommonUserName
maildoc.CreationTime = Date$ + " " + Time$ 'Upto here its saving as one document in the db
MigWord 'this sub routine is not saving as document
maildoc.Status1 = "Draft"
Call maildoc.ComputeWithForm (True, True)
written = written + 1
Call maildoc.Save(True,True)
Else
Exit Do
End If
End With
Loop
Call cuidoc.FieldSetText("SaveOptions", "0")
Call cuidoc.close
Print "Migrated " & written & " Records" & " Done"
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file
Excel.Quit '// Close Excel
Set Excel = Nothing
End Sub
MigWord
Sub MigWord
Dim officeApp As Variant
Dim officeMainDoc As Variant
Dim officeSel As Variant
Dim officeFields As Variant
Dim x As Integer
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim DocFilename As Variant
Dim officedoc As NotesDocument
Dim unid As String
Set officeApp = CreateObject("Word.Application")
On Error Goto ErrExit
DocFilename = doc.TextWLoc
Set officeMainDoc = officeApp.Documents.Open(DocFilename(0))
Set officeFields = officeMainDoc.FormFields
officeFields.Item("Field1").Result=Var1
officeFields.Item("Field2").Result=Var2
officeFields.Item("Field3").Result=Var3
officeFields.Item("Field4").Result=Var4
officeFields.Item("Field5").Result=Var5
officeFields.Item("Field6").Result=Var6
officeFields.Item("Field7").Result=Var7
officeFields.Item("Field8").Result=Var8
officeFields.Item("Field9").Result=Var9
officeFields.Item("Field10").Result=Var10
Call officeMainDoc.Content.Copy
Set maildoc = workspace.ComposeDocument( "", "", "FrmMemo" )
Call maildoc.GoToField( "Body" )
Call maildoc.Paste
Call maildoc.FieldSetText("Status1", "Draft")
Call maildoc.Save
Call maildoc.close
' Quit Application / Close the connection
Call officeApp.Quit(False)
Exit Sub
ErrExit:
Call officeApp.Quit(False)
Msgbox "ErrDoc: " & Err & " - " & Error$
Exit Sub
End Sub
MigWord sub routine is used to replace the variables in word file. Its working fine and pasting the content into the RTF field. But not saving the document.
Can anyone help me out to save all the variables (Content of word file) and other fields into single document.
Note:
I had found some thing that i am creating a new document in the initialize event and againt composing a new document in MigWord sub routine to replace the variables in word document. I think this might causing the problem....
But i tried to edit the same document which is creating in Initialize event by using EditDocument.... But its throwing the error when Pasting the content in the Body (RTF) field.... Telling Property or Method is not available....I think Paste method will works only for UIDOC... Any ideas...
Thanks
Suresh
Print this page
