Using IF to find whether a Merge Field contains a substring

Quick response: as a geneal rule, when you replace a bookmark you're likely
to destroy the bookmark.

Either recreate the bookmark so it's there when you next execute
MailMergeBeforeRecordMerge, or use a "less destructive" approach - e.g. if
your VBA sets the value of a Document Property or Document variable, then a
suiable { DOCPROPERTY } or { DOCVARIABLE } field may be able to insert the
correct string in the document at the point you specified when you created
the mail merge main document.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
I have started using the MailMerge Events, and that seems to be working
pretty well. I am able to use InStr() to check if the list contains the
substring that I am looking for. Now the problem is, inserting the file
that
I need. I included the MailMergeBeforeRecordMerge Event Sub here. I can
from
the Debug.Print statements that I am getting the proper results form the
InStr test. This is the second way that I have tried the insert. The first
way inserted all the documents at the beginning of the Merge Results file.
This method has a problem with the bookmark. It seems to work for the
first
record, but the bookmark does not exist for the second record. Does anyone
notice anything that I could do different to make this work. Thanks.

Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel
As Boolean)
Dim reqCodes As String
Dim curName As String
Dim rng As Word.range

Debug.Print "MailMergeBeforeRecordMerge Event"

curName = Doc.MailMerge.DataSource.DataFields("NAME").Value
reqCodes = Doc.MailMerge.DataSource.DataFields("AWARD_REQ_COD E").Value
Debug.Print curName
Debug.Print reqCodes

Set rng = Doc.Bookmarks("VW0607").range
If InStr(reqCodes, "VW0607") Then
Debug.Print "VW0607"
rng.Collapse Direction:=wdCollapseStart
rng.InsertFile
("C:\Banner_Letters\Special_Text\Verification_Info rmation.doc")
Doc.Bookmarks.Add range:=Selection.range, Name:="VW0607"
Else
Doc.Bookmarks("VW0607").range.Text = " "
End If
End Sub
--
- Joshua Pangborn


"Peter Jamieson" wrote:

Yes, you can use that approach but to get the value for each record you
have
to use Word's MailMerge Events, specifically the BeforeRecordMerge event.
You can't actually create merge fields or modify their data, but you can,
for example, use VBA to test the data then either insert the file you
want
directly, or insert a field that Merge will execute, etc. etc.

The Word VBA Help gives an example of how to use Events if you have not
done
that before. Don't expect the event that fires before the merge starts to
"fire" unless you initiate the merge from the Mail Merge Task Pane, BTW.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
Doug and Peter,

Thanks for the replies. The Convert Text to Table method is not
something
that I would like to do. Once I have this working, it will be run
regularly
by a person who is not as knowledgable about Word, so I need to make it
as
simple as possible for them. The ODBC Datasource option sounds
interesting. I
am going to look into that some more. I just want to bounce an idea off
everyone. I was looking at the VBA for Word reference on MSDN, and saw
that I
could use the MailMergeDataField object to get the value of a
particular
field. Once I assign the value of the field to a variable, I could
easily
search the string for the proper substrings. My question with this idea
is
how to make sure this process happens for each record as I merge the
letters
to a new document. Does this sound like an option, or am I not thinking
clearly? Thanks.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

If this is a one-off, and you have either Excel or Access, I would
probably
either
a. import the data into Access and create a query that uses instr to
detect
the string (cf. Doug's approach), then use that as the data source for
the
merge or
b. import the data into Excel and create a new column that uses INSTR
(I
think - I'm not so familiar with Excel) to do the same.

If you're doing this regulaly on one machine, you might be able to
adapt
approach (a) by linking to the file instead of importing it.

However, what I would probably try first is as follows. There is a lot
to
it, and if you are starting with no knowledge of VBA and ODBC it may
not
be
worth pursuing, but once working, it's a low-maintenance approach, at
least
if used on a single machine.

a. apply the registry patch described in

http://support.microsoft.com/default...b;en-us;825765

b. run a macro based on the following code. If you haven't used Word
VBA
macros before, you may find the following article, and others on the
same
site, useful:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub OpenDataSourceViaODBC

Dim strConnection As String
Dim strQuery As String
Dim strTextFileFolder As String
Dim strTextFileName As String

' Add your own error checking as required

' Set the following string to the pathname of the folder
' containing the data source

strTextFileFolder = "c:\My Data Sources\"

' Set the following string to the file name

strTextFileName = "myfile.txt"

' Construct an ODBC connection string
' (you may not need the last line)

strConnection = _
"DSN=Delimited Text Files;DBQ=" & _
strTextFileFolder & ";" & _
"DriverId=27;FIL=text;"

' Construct a Query string. You need to adapt this to
' do what you need. Let's suppose you are looking for
' the code "abc" in a column named "mycolumn"
' The following SQL should return code as 0 if the string is not found

' Notice that there are three types of quotes in he
' double quotes " to enclose the string
' single quotes ' to enclose strings passed to SQL
' single backquotes ` to surround the alias name "code"
' and the file name (you only need them if the file name contains
' characters such as spaces, and if you leave out "AS `code`"
' SQL will invent a column name for you anyway

strQuery = _
"SELECT instr(1,mycolumn,'abc') AS `code`, * FROM `" & _
strTextFileName & _
"`"

' Ensure any existing data source is closed

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' set the merge type to the one you need. Here, it's for Form Letters

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

' Open the data source

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection := strConnection, _
SQLStatement := strQuery, _
SubType := wdMergeSubTypeWord2000

End Sub

You should only need to do this as a one-off (assuming you don't
change
the
file name, location or query) so you can run it to make the
connection,
then
remove the macro from the template/document if you wish.

However, there are a number of gotchas and things to know about this
code:
a. It relies on the existence of an ODBC User or System DSN called
"Delimited Text Files". Since you're using Word 2003, I think there
will
be
such a DSN on your system but it's not guaranteed. You can create it
if
necessary using the ODBC Administrator (find the Administrative tools
in
Control Panel)
b. it will probably only work with files that have certain extensions
(txt,
csv, possibly one or two others). Further, the end-of-record delimiter
must
be CRLF.
c. it /may/ also rely on the existence of an ODBC info. file called
schema.ini that would be in the same folder as your text file.
schema.ini
contains per-file information about the delimiters and column headers.
You
can maintain the file in the ODBC Administrator or (slightly more
dangerously) in Notepad. For example, a suitable SCHEMA.INI for the
above
file would contain the following. As tested here, Word seemed to be
able
to
read my test file without a Schema.ini.

[myfile.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM

If you need to extract lots of different codes, there is a problem
because
you have to define them all in advance using the instr approach. You
may
run
out of space in the Query (you get about 255 characters, and you may
be
able
to get another 255 by providing an additional string parameter,
SQLStatement1, to OpenDataSource.

If, however, you have certain codes at certain locations in your text,
you
could extract them using left, mid, right functions and create
separate
fields.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates
a
Comma
Delimited Text file which I use in the merge. I have no control over
how
the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote
in
message ...
I am merging some letters, and I have a merge field that lists a
number
of
codes separate by new lines. I need to include another document
if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField =
"*string*",
but that also returns false. Is there another way to test if a
merge
field
contains a string? Thanks. I can provide more information if
needed.
--
- Joshua Pangborn

Thanks for all the help. I got it working last night. I started using the {
DOCVARIABLE } and using it in the { IF } field to insert the document. I was
then having a problem getting the fields to update prior to the record merge.
If I updated all the fields in the document, the merge would only process the
first two records, so I changed it to only update the fields with the {
DOCVARIABLE } and it is working great. This has been a big help. Thanks again.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

Quick response: as a geneal rule, when you replace a bookmark you're likely
to destroy the bookmark.

Either recreate the bookmark so it's there when you next execute
MailMergeBeforeRecordMerge, or use a "less destructive" approach - e.g. if
your VBA sets the value of a Document Property or Document variable, then a
suiable { DOCPROPERTY } or { DOCVARIABLE } field may be able to insert the
correct string in the document at the point you specified when you created
the mail merge main document.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
I have started using the MailMerge Events, and that seems to be working
pretty well. I am able to use InStr() to check if the list contains the
substring that I am looking for. Now the problem is, inserting the file
that
I need. I included the MailMergeBeforeRecordMerge Event Sub here. I can
from
the Debug.Print statements that I am getting the proper results form the
InStr test. This is the second way that I have tried the insert. The first
way inserted all the documents at the beginning of the Merge Results file.
This method has a problem with the bookmark. It seems to work for the
first
record, but the bookmark does not exist for the second record. Does anyone
notice anything that I could do different to make this work. Thanks.

Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel
As Boolean)
Dim reqCodes As String
Dim curName As String
Dim rng As Word.range

Debug.Print "MailMergeBeforeRecordMerge Event"

curName = Doc.MailMerge.DataSource.DataFields("NAME").Value
reqCodes = Doc.MailMerge.DataSource.DataFields("AWARD_REQ_COD E").Value
Debug.Print curName
Debug.Print reqCodes

Set rng = Doc.Bookmarks("VW0607").range
If InStr(reqCodes, "VW0607") Then
Debug.Print "VW0607"
rng.Collapse Direction:=wdCollapseStart
rng.InsertFile
("C:\Banner_Letters\Special_Text\Verification_Info rmation.doc")
Doc.Bookmarks.Add range:=Selection.range, Name:="VW0607"
Else
Doc.Bookmarks("VW0607").range.Text = " "
End If
End Sub
--
- Joshua Pangborn


"Peter Jamieson" wrote:

Yes, you can use that approach but to get the value for each record you
have
to use Word's MailMerge Events, specifically the BeforeRecordMerge event.
You can't actually create merge fields or modify their data, but you can,
for example, use VBA to test the data then either insert the file you
want
directly, or insert a field that Merge will execute, etc. etc.

The Word VBA Help gives an example of how to use Events if you have not
done
that before. Don't expect the event that fires before the merge starts to
"fire" unless you initiate the merge from the Mail Merge Task Pane, BTW.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
Doug and Peter,

Thanks for the replies. The Convert Text to Table method is not
something
that I would like to do. Once I have this working, it will be run
regularly
by a person who is not as knowledgable about Word, so I need to make it
as
simple as possible for them. The ODBC Datasource option sounds
interesting. I
am going to look into that some more. I just want to bounce an idea off
everyone. I was looking at the VBA for Word reference on MSDN, and saw
that I
could use the MailMergeDataField object to get the value of a
particular
field. Once I assign the value of the field to a variable, I could
easily
search the string for the proper substrings. My question with this idea
is
how to make sure this process happens for each record as I merge the
letters
to a new document. Does this sound like an option, or am I not thinking
clearly? Thanks.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

If this is a one-off, and you have either Excel or Access, I would
probably
either
a. import the data into Access and create a query that uses instr to
detect
the string (cf. Doug's approach), then use that as the data source for
the
merge or
b. import the data into Excel and create a new column that uses INSTR
(I
think - I'm not so familiar with Excel) to do the same.

If you're doing this regulaly on one machine, you might be able to
adapt
approach (a) by linking to the file instead of importing it.

However, what I would probably try first is as follows. There is a lot
to
it, and if you are starting with no knowledge of VBA and ODBC it may
not
be
worth pursuing, but once working, it's a low-maintenance approach, at
least
if used on a single machine.

a. apply the registry patch described in

http://support.microsoft.com/default...b;en-us;825765

b. run a macro based on the following code. If you haven't used Word
VBA
macros before, you may find the following article, and others on the
same
site, useful:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub OpenDataSourceViaODBC

Dim strConnection As String
Dim strQuery As String
Dim strTextFileFolder As String
Dim strTextFileName As String

' Add your own error checking as required

' Set the following string to the pathname of the folder
' containing the data source

strTextFileFolder = "c:\My Data Sources\"

' Set the following string to the file name

strTextFileName = "myfile.txt"

' Construct an ODBC connection string
' (you may not need the last line)

strConnection = _
"DSN=Delimited Text Files;DBQ=" & _
strTextFileFolder & ";" & _
"DriverId=27;FIL=text;"

' Construct a Query string. You need to adapt this to
' do what you need. Let's suppose you are looking for
' the code "abc" in a column named "mycolumn"
' The following SQL should return code as 0 if the string is not found

' Notice that there are three types of quotes in he
' double quotes " to enclose the string
' single quotes ' to enclose strings passed to SQL
' single backquotes ` to surround the alias name "code"
' and the file name (you only need them if the file name contains
' characters such as spaces, and if you leave out "AS `code`"
' SQL will invent a column name for you anyway

strQuery = _
"SELECT instr(1,mycolumn,'abc') AS `code`, * FROM `" & _
strTextFileName & _
"`"

' Ensure any existing data source is closed

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' set the merge type to the one you need. Here, it's for Form Letters

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

' Open the data source

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection := strConnection, _
SQLStatement := strQuery, _
SubType := wdMergeSubTypeWord2000

End Sub

You should only need to do this as a one-off (assuming you don't
change
the
file name, location or query) so you can run it to make the
connection,
then
remove the macro from the template/document if you wish.

However, there are a number of gotchas and things to know about this
code:
a. It relies on the existence of an ODBC User or System DSN called
"Delimited Text Files". Since you're using Word 2003, I think there
will
be
such a DSN on your system but it's not guaranteed. You can create it
if
necessary using the ODBC Administrator (find the Administrative tools
in
Control Panel)
b. it will probably only work with files that have certain extensions
(txt,
csv, possibly one or two others). Further, the end-of-record delimiter
must
be CRLF.
c. it /may/ also rely on the existence of an ODBC info. file called
schema.ini that would be in the same folder as your text file.
schema.ini
contains per-file information about the delimiters and column headers.
You
can maintain the file in the ODBC Administrator or (slightly more
dangerously) in Notepad. For example, a suitable SCHEMA.INI for the
above
file would contain the following. As tested here, Word seemed to be
able
to
read my test file without a Schema.ini.

[myfile.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM

If you need to extract lots of different codes, there is a problem
because
you have to define them all in advance using the instr approach. You
may
run
out of space in the Query (you get about 255 characters, and you may
be
able
to get another 255 by providing an additional string parameter,
SQLStatement1, to OpenDataSource.

If, however, you have certain codes at certain locations in your text,
you
could extract them using left, mid, right functions and create
separate
fields.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates
a
Comma
Delimited Text file which I use in the merge. I have no control over
how
the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote
in
message ...
I am merging some letters, and I have a merge field that lists a
number
of
codes separate by new lines. I need to include another document
if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField =
"*string*",
but that also returns false. Is there another way to test if a
merge
field
contains a string? Thanks. I can provide more information if