sorry for cross posting... I thought it better fitted the "formscoding"
forum, but is related to this original thread.
After debating how to get my final report output, it looks like using Word
Automation is the best way.
On the advice of a previous post (thanks to Peter Hibbs) , I created the
module shown below. I ensured the references for Microsoft Word xx.0 (I am
runnung MS Word 9.0) and DAO 3.6 Object library are checked in the "Tools" /
"References" menu.
However when I run the module, I get the error:
"Compile error sub or function not defined"
with the Debug highlighting
"ReplaceText"
could anyone advise what I am doing wrong?
Thanks
Bruce
'------------------------------------------------------------------------
Public Sub PrintappmntLetter(vID As Long, vFilename As String)
'Print personalised letter using MS Word
'Entry (vID) holds unique ID of record to be printed
' (vFilename) holds pathname of document to be printed
'Ensure that these references are active-
'Microsoft DAO 3.6 Object Library
'Microsoft Word xx.0 Object Library (xx = your version of Word)
Dim objWord As Word.Application
Dim rst As Recordset
On Error GoTo ErrorCode
'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = False
'Fetch data for specified record from table
Set rst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblMembers WHERE ID = " & vID)
ReplaceText objWord, "[ctitle]", Nz(rst!Title)
ReplaceText objWord, "[FName]", Nz(rst!firstname)
ReplaceText objWord, "[LName]", Nz(rst!surname)
ReplaceText objWord, "[CDOB]", Nz(rst!clDOB)
ReplaceText objWord, "[DateAcc]", Nz(rst!AccDate)
ReplaceText objWord, "[ExpertDet]", Nz(rst!Expert)
ReplaceText objWord, "[ExpertQuals]", Nz(rst!expquals)
ReplaceText objWord, "[ExpertAdd1]", Nz(rst!SurgeryAddr1)
ReplaceText objWord, "[ExpertAdd2]", Nz(rst!SurgeryAddr2)
ReplaceText objWord, "[ExpertAdd3]", Nz(rst!SurgeryAddr3)
ReplaceText objWord, "[ExpertAddPC]", Nz(rst!SurgeryPostCode)
ReplaceText objWord, "[ExpertPhone]", Nz(rst!ExpPhone)
ReplaceText objWord, "[ClaimAdd1]", Nz(rst!ClaimAddr1)
ReplaceText objWord, "[ClaimAdd2]", Nz(rst!ClaimAddr2)
ReplaceText objWord, "[ClaimAdd3]", Nz(rst!ClaimAddr3)
ReplaceText objWord, "[ClaimAddPC]", Nz(rst!ClaimPostCode)
'add other fields here, as reqd
rst.Close
Set rst = Nothing
objWord.ActiveDocument.Saved = True
objWord.ScreenUpdating = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub
ErrorCode:
objWord.Quit
MsgBox Err.Description
End Sub
'--------------------------------------------------------------------------------
Public Sub ReplaceText(obj As Word.Application, vSource As String,
vDest As String)
'Replace all occurences of vSource with vDest in Word doc
obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll
End Sub
Peter Hibbs said:
Chlaris,
Use Word Automation to copy data from your database into a Word
document. First create a Word document and enter control codes in the
document where you want data to be printed. For example, I have used
[FN] for first name, [LN] for last name, [TP] for telephone number and
so on. You need to use codes which are not normally likely to be in
the document.
Create a new Module and save it as modWordCode (or whatever you want).
Then copy and paste the following code into the module and save again.
'------------------------------------------------------------------------
Public Sub PrintLetter(vID As Long, vFilename As String)
'Print personalised letter using MS Word
'Entry (vID) holds unique ID of record to be printed
' (vFilename) holds pathname of document to be printed
'Ensure that these references are active-
'Microsoft DAO 3.6 Object Library
'Microsoft Word xx.0 Object Library (xx = your version of Word)
Dim objWord As Word.Application
Dim rst As Recordset
On Error GoTo ErrorCode
'Start MS Word and open specified document
Set objWord = New Word.Application
objWord.Documents.Add vFilename
objWord.ScreenUpdating = False
'Fetch data for specified record from table
Set rst = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblMembers WHERE ID = " & vID)
ReplaceText objWord, "[FN]", Nz(rst!FirstName)
ReplaceText objWord, "[LN]", Nz(rst!LastName)
ReplaceText objWord, "[TP]", Nz(rst!Telephone)
ReplaceText objWord, "[FX]", Nz(rst!Fax)
'add other fields here, as reqd
rst.Close
Set rst = Nothing
objWord.ActiveDocument.Saved = True
objWord.ScreenUpdating = True
objWord.Visible = True
Set objWord = Nothing
Exit Sub
ErrorCode:
objWord.Quit
MsgBox Err.Description
End Sub
'--------------------------------------------------------------------------------
Public Sub ReplaceText(obj As Word.Application, vSource As String,
vDest As String)
'Replace all occurences of vSource with vDest in Word doc
obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _
Replace:=wdReplaceAll
End Sub
'--------------------------------------------------------------------------------
If you get any lines shown in red then edit the code so that it is all
on one line. Change the name of the table and fields to suit your own
requirements. Basically what happens is that the document is opened,
then a recordset is created with a list of the fields required and the
'control codes' in the document are replaced with the data from the
recordset.
To print your Word document just call the routine from your form like
this :-
PrintLetter ID, "C:\Temp\Test Letter.doc"
where ID is a unique value which identifies the record in the table
from which you want to extract the record data, i.e. FirstName,
LastName, etc, etc. The second argument is the pathname to the Word
document, of course.
HTH
Peter Hibbs.
So can someone in here help me how to write the code ?
Please help.
Thanks.
