Word Merge and

D

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
 
E

ErezM via AccessMonster.com

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
Click to expand...
 
D

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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Try changing

Dim rst As Recordset

to

Dim rst As DAO.Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


DRBE said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Insert fields from ACCESS into OPen Office writer 0
Merge with Word: memo field too long for string 6
Wildcards in obj.ActiveDocument.Content.Find.Execute 3
Setting Focus to Word 4
Word mail merge code problem 2
Transfer multiple records to Word bookmark 4
Letter merge using backend data 2
WORD - Excel automation 0

Top