Word Merge and

[DRBE]

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

 

[DRBE]

Thanks that helped,
I have now managed to get rid of that error using both methods (previously
the ReplacementText sub, one of the lines of text had been carried onto a new
line, causing an error)

Now I get an error box with the heading "Microsoft Access" and the error
"Type mismatch".

the code seems to run OK, does not open the "DEBUG" window the way it was
before, presumably the problem is writing to the WORD doc.

presumably one of the fields is of a wrong type.
Even removing the replace text bit completely the error remains, so it does
not look related to the actual fields that are being replaced.

Is there anyway of localising what the "mismatch" is referring to?

regards
Bruce

:

hi
i'm not sure what the problem is, but since it's in ReplaceText sub, you can
try to do without it

use

objWord.ActiveDocument.Content.Find.Execute FindText:="[ctitle]", _
ReplaceWith:=Nz(rst!Title), Format:=True, _
Replace:=wdReplaceAll

instead of the ReplaceText... lines you have (try with the first one and see
where the error is),
it's resource economic too, cause you dont create as many work objects, only
one

good luck
Erez

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

 

[Douglas J. Steele]

Try changing

Dim rst As Recordset

to

Dim rst As DAO.Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks that helped,
I have now managed to get rid of that error using both methods (previously
the ReplacementText sub, one of the lines of text had been carried onto a
new
line, causing an error)

Now I get an error box with the heading "Microsoft Access" and the error
"Type mismatch".

the code seems to run OK, does not open the "DEBUG" window the way it was
before, presumably the problem is writing to the WORD doc.

presumably one of the fields is of a wrong type.
Even removing the replace text bit completely the error remains, so it
does
not look related to the actual fields that are being replaced.

Is there anyway of localising what the "mismatch" is referring to?

regards
Bruce

:

hi
i'm not sure what the problem is, but since it's in ReplaceText sub, you
can
try to do without it

use

objWord.ActiveDocument.Content.Find.Execute FindText:="[ctitle]", _
ReplaceWith:=Nz(rst!Title), Format:=True, _
Replace:=wdReplaceAll

instead of the ReplaceText... lines you have (try with the first one and
see
where the error is),
it's resource economic too, cause you dont create as many work objects,
only
one

good luck
Erez

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

 

[DRBE]

Thanks everyone for your help.

as it turns out, I was being a Dimwit... I used the form name instead of the
source table name. it is working well now.
Hopefully I'll eventually get my database working up to my original
expectations
Bruce