Access Query Set to Mail Merge for 300 docs.

[Kerri]

Hi,
I have ~ 300 Word 2000 files that need to be setup as a mail merge from an
Access Query. I was hoping someone else has had the same issue. The
letters were once all WordPerfect mail merge files that have been converted.
I tried the generic - record and edit - but I don't understand what causes
it to debug.

I would also like to be able to 'find' any fields like [42] and replace it
with the corresponding Access field name.

Thank you in advance for your suggestions.
Kerri

 

[Doug Robbins - Word MVP]

Hi Kerri,

Record and Edit would not be of much use for something like this.

Some of what you will need is in the article “How to Find & ReplaceAll on a
Batch of Documents in the Same Folder” at:

http://www.mvps.org/word/FAQs/MacrosVBA/BatchFR.htm

but there will be a bit more coding required to be done. It is however
do-able.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Kerri]

Thank you Doug!

I will read up on that ariticle and give it a few tests!

Hi Kerri,

Record and Edit would not be of much use for something like this.

Some of what you will need is in the article "How to Find & ReplaceAll on a
Batch of Documents in the Same Folder" at:

http://www.mvps.org/word/FAQs/MacrosVBA/BatchFR.htm

but there will be a bit more coding required to be done. It is however
do-able.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi,
I have ~ 300 Word 2000 files that need to be setup as a mail merge from an
Access Query. I was hoping someone else has had the same issue. The
letters were once all WordPerfect mail merge files that have been converted.
I tried the generic - record and edit - but I don't understand what causes
it to debug.

I would also like to be able to 'find' any fields like [42] and replace it
with the corresponding Access field name.

Thank you in advance for your suggestions.
Kerri

 

[Kerri]

Hi Doug,
I have decided to scrap the "change all documents at one time" idea and
decided to concentrate on the Find a merge field in the current document and
replace with a different field name. Could you lead me in the right
direction?

Here is what I have learned:
1. Must do Alt + F9 to display the field code prior to trying to "find" the
code.
2. the "^d" (field) option *doesn't* work in find and replace like it
should.
3. the "^d" (field) even if it did work isn't available in the replace
options.

The below code does "find" the text if it is in a field and replaces it.

How do I set the .Text = (some number to go through)

In other words I have an excel spreadsheet that has every field number 1- 60
in the first column, in the next I list the Access database's Field Name for
the Merge field. I want to say Find field 1 and replace with the text in
column 2. Increasing the Field number by one for each pass.

OR

Is it easier to code what each field text represents each number in VBA.
Kinda like a Select Case?? Or is this only for userforms?

i = 1
Select Case i
case 1: "Decedents_LName"
case 2: "Decedents_FName" ' and here i might want to make it \* Upper for
formatting

Sub FindField()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Customer_phone_no" ' this field will vary and will be a
number
.Replacement.Text = "My_Phone_no"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
With Selection
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseStart
Else
.Collapse Direction:=wdCollapseEnd
End If
.Find.Execute Replace:=wdReplaceOne
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseEnd
Else
.Collapse Direction:=wdCollapseStart
End If
.Find.Execute
End With
End Sub

Thanks for you help!
Kerri
P.S. When you say: "Unsolicited questions forwarded directly to me will
only be answered on a paid consulting basis." Does this mean when we reply
to the group don't include your name in the cc box??? Or just don't contact
me on topics not discussed in the newsgroups?

Thank you Doug!

I will read up on that ariticle and give it a few tests!

Hi Kerri,

Record and Edit would not be of much use for something like this.

Some of what you will need is in the article "How to Find & ReplaceAll

on

a
Batch of Documents in the Same Folder" at:

http://www.mvps.org/word/FAQs/MacrosVBA/BatchFR.htm

but there will be a bit more coding required to be done. It is however
do-able.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

from

an

Access Query. I was hoping someone else has had the same issue. The
letters were once all WordPerfect mail merge files that have been converted.
I tried the generic - record and edit - but I don't understand what causes
it to debug.

I would also like to be able to 'find' any fields like [42] and

replace

it

 

[Doug Robbins - Word MVP]

Hi Kerri,

The easiest thing will probably be to set up a query in access where you
have for example the field My_Phone_no displayed as Customer_phone_no.

To do this in Access, you would create a new select query based on the
table. Then select all of the fields where the table appears in the upper
window of the Query builder and drag into the first cell in the first row of
the QBE grid. Then go to each field in turn and using the above as an
example, type

Customer_phone_no.:

in front of the My_Phone_no

When you view the results of the query, the "field name" for that particular
column of data will then appear as Customer_Phone_no.

Then you can leave the 300 documents as they are.

In response to you PS, you can CC me, but the only questions that I will
answer without charge are those that are also posted to the newsgroup, which
is were I am answering this question.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi Doug,
I have decided to scrap the "change all documents at one time" idea and
decided to concentrate on the Find a merge field in the current document and
replace with a different field name. Could you lead me in the right
direction?

Here is what I have learned:
1. Must do Alt + F9 to display the field code prior to trying to "find" the
code.
2. the "^d" (field) option *doesn't* work in find and replace like it
should.
3. the "^d" (field) even if it did work isn't available in the replace
options.

The below code does "find" the text if it is in a field and replaces it.

How do I set the .Text = (some number to go through)

In other words I have an excel spreadsheet that has every field number 1- 60
in the first column, in the next I list the Access database's Field Name for
the Merge field. I want to say Find field 1 and replace with the text in
column 2. Increasing the Field number by one for each pass.

OR

Is it easier to code what each field text represents each number in VBA.
Kinda like a Select Case?? Or is this only for userforms?

i = 1
Select Case i
case 1: "Decedents_LName"
case 2: "Decedents_FName" ' and here i might want to make it \* Upper for
formatting

Sub FindField()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Customer_phone_no" ' this field will vary and will be a
number
.Replacement.Text = "My_Phone_no"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
With Selection
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseStart
Else
.Collapse Direction:=wdCollapseEnd
End If
.Find.Execute Replace:=wdReplaceOne
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseEnd
Else
.Collapse Direction:=wdCollapseStart
End If
.Find.Execute
End With
End Sub

Thanks for you help!
Kerri
P.S. When you say: "Unsolicited questions forwarded directly to me will
only be answered on a paid consulting basis." Does this mean when we reply
to the group don't include your name in the cc box??? Or just don't contact
me on topics not discussed in the newsgroups?

Thank you Doug!

I will read up on that ariticle and give it a few tests!

Hi Kerri,

Record and Edit would not be of much use for something like this.

Some of what you will need is in the article "How to Find & ReplaceAll

on

a
Batch of Documents in the Same Folder" at:

http://www.mvps.org/word/FAQs/MacrosVBA/BatchFR.htm

but there will be a bit more coding required to be done. It is however
do-able.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
Hi,
I have ~ 300 Word 2000 files that need to be setup as a mail merge

from

an
Access Query. I was hoping someone else has had the same issue. The
letters were once all WordPerfect mail merge files that have been
converted.
I tried the generic - record and edit - but I don't understand what causes
it to debug.

I would also like to be able to 'find' any fields like [42] and

replace

it
with the corresponding Access field name.

Thank you in advance for your suggestions.
Kerri

 

[Kerri]

Hi Doug,

OK....interesting, I never thought about going that route. What about
having to put format codes in the merge field. I was planning on writing
that in to the merge code.

For instance field <<1>> is suppose to be ALL CAPS which is the merge field
code
"\* Upper". Wouldn't I still have to do a find and replace on all
instances of that code in the document...because there will be times that
code will be regular case. From my past experience, Access doesn't hold
formatting going down to word, right?

Thank you for your time & expertise!! I'll work on the field changes in the
Access Query tomorrow.

Thank you again. =)
Kerri

Hi Kerri,

The easiest thing will probably be to set up a query in access where you
have for example the field My_Phone_no displayed as Customer_phone_no.

To do this in Access, you would create a new select query based on the
table. Then select all of the fields where the table appears in the upper
window of the Query builder and drag into the first cell in the first row of
the QBE grid. Then go to each field in turn and using the above as an
example, type

Customer_phone_no.:

in front of the My_Phone_no

When you view the results of the query, the "field name" for that particular
column of data will then appear as Customer_Phone_no.

Then you can leave the 300 documents as they are.

In response to you PS, you can CC me, but the only questions that I will
answer without charge are those that are also posted to the newsgroup, which
is were I am answering this question.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi Doug,
I have decided to scrap the "change all documents at one time" idea and
decided to concentrate on the Find a merge field in the current document and
replace with a different field name. Could you lead me in the right
direction?

Here is what I have learned:
1. Must do Alt + F9 to display the field code prior to trying to "find" the
code.
2. the "^d" (field) option *doesn't* work in find and replace like it
should.
3. the "^d" (field) even if it did work isn't available in the replace
options.

The below code does "find" the text if it is in a field and replaces it.

How do I set the .Text = (some number to go through)

In other words I have an excel spreadsheet that has every field number

1-

60
in the first column, in the next I list the Access database's Field Name for
the Merge field. I want to say Find field 1 and replace with the text in
column 2. Increasing the Field number by one for each pass.

OR

Is it easier to code what each field text represents each number in VBA.
Kinda like a Select Case?? Or is this only for userforms?

i = 1
Select Case i
case 1: "Decedents_LName"
case 2: "Decedents_FName" ' and here i might want to make it \* Upper for
formatting

Sub FindField()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Customer_phone_no" ' this field will vary and will be a
number
.Replacement.Text = "My_Phone_no"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
With Selection
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseStart
Else
.Collapse Direction:=wdCollapseEnd
End If
.Find.Execute Replace:=wdReplaceOne
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseEnd
Else
.Collapse Direction:=wdCollapseStart
End If
.Find.Execute
End With
End Sub

Thanks for you help!
Kerri
P.S. When you say: "Unsolicited questions forwarded directly to me will
only be answered on a paid consulting basis." Does this mean when we reply
to the group don't include your name in the cc box??? Or just don't contact
me on topics not discussed in the newsgroups?

Thank you Doug!

I will read up on that ariticle and give it a few tests!


Hi Kerri,

Record and Edit would not be of much use for something like this.

Some of what you will need is in the article "How to Find &

ReplaceAll
on

a
Batch of Documents in the Same Folder" at:

http://www.mvps.org/word/FAQs/MacrosVBA/BatchFR.htm

but there will be a bit more coding required to be done. It is however
do-able.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
Hi,
I have ~ 300 Word 2000 files that need to be setup as a mail merge from
an
Access Query. I was hoping someone else has had the same issue. The
letters were once all WordPerfect mail merge files that have been
converted.
I tried the generic - record and edit - but I don't understand what
causes
it to debug.

I would also like to be able to 'find' any fields like [42] and replace
it
with the corresponding Access field name.

Thank you in advance for your suggestions.
Kerri

 

[Doug Robbins - Word MVP]

Hi Kerri,

That can be done just as well, (and considering that you are talking about
300 mail merge main documents, almost certainly better) in the query, using
the Format() function.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi Doug,

OK....interesting, I never thought about going that route. What about
having to put format codes in the merge field. I was planning on writing
that in to the merge code.

For instance field <<1>> is suppose to be ALL CAPS which is the merge field
code
"\* Upper". Wouldn't I still have to do a find and replace on all
instances of that code in the document...because there will be times that
code will be regular case. From my past experience, Access doesn't hold
formatting going down to word, right?

Thank you for your time & expertise!! I'll work on the field changes in the
Access Query tomorrow.

Thank you again. =)
Kerri

Hi Kerri,

The easiest thing will probably be to set up a query in access where you
have for example the field My_Phone_no displayed as Customer_phone_no.

To do this in Access, you would create a new select query based on the
table. Then select all of the fields where the table appears in the upper
window of the Query builder and drag into the first cell in the first

row

of
the QBE grid. Then go to each field in turn and using the above as an
example, type

Customer_phone_no.:

in front of the My_Phone_no

When you view the results of the query, the "field name" for that particular
column of data will then appear as Customer_Phone_no.

Then you can leave the 300 documents as they are.

In response to you PS, you can CC me, but the only questions that I will
answer without charge are those that are also posted to the newsgroup, which
is were I am answering this question.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
document
and "find"
the 1- Name
for

text

in

be

a

number
.Replacement.Text = "My_Phone_no"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
With Selection
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseStart
Else
.Collapse Direction:=wdCollapseEnd
End If
.Find.Execute Replace:=wdReplaceOne
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseEnd
Else
.Collapse Direction:=wdCollapseStart
End If
.Find.Execute
End With
End Sub

Thanks for you help!
Kerri
P.S. When you say: "Unsolicited questions forwarded directly to me will
only be answered on a paid consulting basis." Does this mean when we reply
to the group don't include your name in the cc box??? Or just don't contact
me on topics not discussed in the newsgroups?

Thank you Doug!

I will read up on that ariticle and give it a few tests!


Hi Kerri,

Record and Edit would not be of much use for something like this.

Some of what you will need is in the article "How to Find & ReplaceAll
on
a
Batch of Documents in the Same Folder" at:

http://www.mvps.org/word/FAQs/MacrosVBA/BatchFR.htm

but there will be a bit more coding required to be done. It is however
do-able.

Please post any further questions or followup to the newsgroups

for
the

benefit of others who may be interested. Unsolicited questions
forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
Hi,
I have ~ 300 Word 2000 files that need to be setup as a mail merge
from
an
Access Query. I was hoping someone else has had the same issue. The
letters were once all WordPerfect mail merge files that have been
converted.
I tried the generic - record and edit - but I don't understand what
causes
it to debug.

I would also like to be able to 'find' any fields like [42] and
replace
it
with the corresponding Access field name.

Thank you in advance for your suggestions.
Kerri