Using IF to find whether a Merge Field contains a substring

[Joshua Pangborn]

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.

 

[Doug Robbins - Word MVP]

You are not going to be able to do that with an IF field during the mail
merge process. What you will have to do is modify the data source so that
the string is in a separate field. Just how you go about doing that will
depend upon the data source. It will probably require a macro to do it, but
it is not too difficult.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

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

Peter Jamieson

 

[macropod]

Hi Joshua,

This is easy enough to do with wildcards in the field coding if the
substring always starts at the same position in the overall string. For
example, if the substring to look for always begins at the 5th character:

{IF{MergeField MyData}= ????TestString* "True Result" False Result"}

For anything involving variable string positioning, a field-code solution
could be horribly complex.

Cheers

 

[Joshua Pangborn]

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.

 

[Doug Robbins - Word MVP]

It's going to be a lot easier to achieve what you are after if the data is
converted into a table, which can be done by opening the csv file in Word
and then selecting everything and use the Convert Text to Table item on the
Tables toolbar. Then can use a macro to iterate through the cells in the
column that may or may not contain the string and use the InStr() function
to determine if it is present. What I would do is have the macro add
another column to the table and populate that with the result of the InStr()
function which will be 0 if the string does not exist or something greater
than 0 if it does. Then, if you use that table as the data source for the
merge, you can have the If...then...Else field check on the values that are
in that new column and include the other document if appropriate.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

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.aspx?scid=kb;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 here:
' 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]

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

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.aspx?scid=kb;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 here:
' 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

 

[Peter Jamieson]

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

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

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.aspx?scid=kb;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 here:
' 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

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


:

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

Peter Jamieson
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]

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_CODE").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_Information.doc")
Doc.Bookmarks.Add range:=Selection.range, Name:="VW0607"
Else
Doc.Bookmarks("VW0607").range.Text = " "
End If
End Sub
--
- Joshua Pangborn

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

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

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.aspx?scid=kb;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 here:
' 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

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


:

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

Peter Jamieson
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.

 

[Peter Jamieson]

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

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_CODE").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_Information.doc")
Doc.Bookmarks.Add range:=Selection.range, Name:="VW0607"
Else
Doc.Bookmarks("VW0607").range.Text = " "
End If
End Sub
--
- Joshua Pangborn

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

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


:

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.aspx?scid=kb;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 here:
' 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

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


:

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

Peter Jamieson
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

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

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_CODE").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_Information.doc")
Doc.Bookmarks.Add range:=Selection.range, Name:="VW0607"
Else
Doc.Bookmarks("VW0607").range.Text = " "
End If
End Sub
--
- Joshua Pangborn

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

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


:

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.aspx?scid=kb;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 here:
' 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

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


:

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

Peter Jamieson
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