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