Word mailmerge question

[jdbruijn76]

Hi,

I'm trying to figure out how to best use word mailmerge from my
application. In my application there is a difference in the Display
name of a field and the fieldname in the database. I want the user to
be able to use the (user-friendly) displayname when creating the merge
template, but when I use the displayname as the fieldcode I will run
into problem when the user changes the displayname inside my
application. So is it possible to show the user the Displayname when he
is creating the template, but internaly in the mailmerge template to
use the dbfieldname in the fieldcode?

Jos.

 

[Peter Jamieson]

Typically the best way to do this, if your data source allows it, is to
define a Query (or View) based on the table you want to use, and give the
columns of the View/Query the names you want the user to see.Then use the
query/view as the data source for the merge.

Typically the Query/View just defines an alias for each column in SQL, along
the lines of

SELECT column1 As `username1`, column2 As `username2` FROM table1

In some cases you may be able to do that by issuing the appropriate SQL in
Word's OpenDataSource method call rather than by creating a Query/View in
Access/SQL Server or whatever, but only if
a. the dialect of SQL supports it
b. the SQL statement is not too long (there's a limit of around 255/511
characters, depending on the data source and connection type).



Alternatively if you are opening the datasource in VBA code in some cases
you may be able to do this

 

[jdbruijn76]

I'm not sure how I can use this. My application generates a datasource
(textfile) in the format:
"Code","First Name","Last Name"
When the user creates the Mailmerge Template he sees those names
instead of ID,FirstNm,LastNm. The template will then have the
mergefield: <Last_Name>
Now has the user the option to change the displayname of this field
i.e. he changes it in "Name", the next time he tries to generate the
document the datasource will have the following headers:
"Code","First Name","Name" so the field <Last_Name> gives an error. Is
it possible to show the user <Last_Name> but internally store <LastNm>?

My datasource is created by a Delphi application and linked using:

aMergeDoc.MailMerge.OpenDataSource(aFN, ....);
where aMergeDoc: _Document and aFN is the filename of the txt file.

Jos

 

[Peter Jamieson]

If I understood correctly what you are trying to do, I think it will be hard
to do it reliably precisely because the user can change the display names in
your application, unless your application always redefines the relationship
between the display name and the (presumably) fixed internal name each time
the user /uses/ a template. The only thing that I think comes close is what
I suggested before, i.e.
a. generate your data source file using your internal names (LastNm) etc.
b. dynamically generate the necessary SQL using the user-defined names and
use it in the OpenDataSource call. That will only work in this case if the
OpenDataSource call opens the text file using Jet SQL (not Word's very
simple built-in SQL dialect), which probably means you have to open the file
using an ODBC DSN, e.g. (in VBA) for a file called c:\a\abc.txt, something
like

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:= "DSN=Delimited Text
Files;DBQ=C:\a;DefaultDir=C:\a;DriverId=27;FIL=text;", _
SQLStatement:="SELECT Code As `display name for code`,FirstNm As `Display
name for FirstNm FROM abc.txt", _
SubType:=wdMergeSubTypeWord2000

(you only need the last parameter in Word XP and later)

Peter Jamieson