Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
Hi all,
I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
You can't do this with fields. You will have to create another initial field
in your data file. This is easy enough to do in (eg) Excel. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mark wrote: Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
I would go Graham's route, but if you cannot do that, you can probably have
26 IF fields that do e,.g. { IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" "" }{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" "" } and so on... Peter Jamieson "Mark" wrote in message ... Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
That's /exactly/ the type of text manipulation I've been able to do in
WP in its merge language and have failed to figure out how to do in Word. At least now I know not to waste my time. I had a routine to reduce the full name of an insurance company to a short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I can't modify the merge field: I just get a .dbf file straight from IT. I've been trying to figure out some way around Word's limitations. I posted a message a while ago, and got a somewhat unhelpful answer to forget what I could do in WP and move on to learn SQL or something. WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever to the variable vTxt (in this case, a field), and string commands, so then you can search for, say, the position of the first space with a string position command like STRPOS(" ";vTxt) and combine it with a substring command to subtract the bit you want and assign that to your variable. Sigh. Is there no way to invoke VBA? You know, somehow assign that field with the name to a variable, then invoke VBA to manipulate it, then insert the modified variable into the document? Again, I've done that with fields where I get want the user to be able to modify the info if need be, but not necessarily. [The routine is: 1. assign field to variable.. 2. invoke macro with prompt where variable pre-fills the prompt. 3. user can modify or not the variable. 4. macro stops, variable is inserted into text in WP's merge language.] But so far I haven't found the equivalent of WP's VARIABLE(vTxt) command to insert the modified-or-not variable into the text... David Evans Peter Jamieson wrote: I would go Graham's route, but if you cannot do that, you can probably have 26 IF fields that do e,.g. { IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" "" }{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" "" } and so on... Peter Jamieson "Mark" wrote in message ... Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
You can use the .dbf file as the datasource for a catalog (or in Word XP and
later it is called directory) type mailmerge, in the main document of which you insert the mergefields inside the cells of a one row table. When you execute that merge to a new document, the document that is produced will contain a table with a row of data for each record in the datasource. You could then use a macro to iterate through the rows of that table to do whatever you want with the data. It it is always the same thing, all of the above could be automated, leaving you then with a table of data that can be used as the data source for your original mailmerge. -- 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 "fscodave" wrote in message oups.com... That's /exactly/ the type of text manipulation I've been able to do in WP in its merge language and have failed to figure out how to do in Word. At least now I know not to waste my time. I had a routine to reduce the full name of an insurance company to a short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I can't modify the merge field: I just get a .dbf file straight from IT. I've been trying to figure out some way around Word's limitations. I posted a message a while ago, and got a somewhat unhelpful answer to forget what I could do in WP and move on to learn SQL or something. WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever to the variable vTxt (in this case, a field), and string commands, so then you can search for, say, the position of the first space with a string position command like STRPOS(" ";vTxt) and combine it with a substring command to subtract the bit you want and assign that to your variable. Sigh. Is there no way to invoke VBA? You know, somehow assign that field with the name to a variable, then invoke VBA to manipulate it, then insert the modified variable into the document? Again, I've done that with fields where I get want the user to be able to modify the info if need be, but not necessarily. [The routine is: 1. assign field to variable.. 2. invoke macro with prompt where variable pre-fills the prompt. 3. user can modify or not the variable. 4. macro stops, variable is inserted into text in WP's merge language.] But so far I haven't found the equivalent of WP's VARIABLE(vTxt) command to insert the modified-or-not variable into the text... David Evans Peter Jamieson wrote: I would go Graham's route, but if you cannot do that, you can probably have 26 IF fields that do e,.g. { IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" "" }{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" "" } and so on... Peter Jamieson "Mark" wrote in message ... Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
Unfortunately, it's difficult to know how to reply to people who have made
full use of WP's facilities: it arguably always had a better way to do merges. The basic problems with Word compared with WP (as I understand it from a brief encounter with WP rather a long time ago!) are that a. Word Macros are basically tied to Word templates and documents and lack the independence of the sort of macros you could write in WP b. Word MailMerge assumes a single tabular data source (i.e. same fields in each row) whereas you could, in effect, structure a WP data source how you wanted c. Word's field language is poor (in fact I think its development was basically abandoned in favour of effort on VBA, despite the fact that the two things work in completely different ways. I had a routine to reduce the full name of an insurance company to a short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I can't modify the merge field: I just get a .dbf file straight from IT. I've been trying to figure out some way around Word's limitations. I posted a message a while ago, and got a somewhat unhelpful answer to forget what I could do in WP and move on to learn SQL or something. If that message was from me, it would not have been posted without thought. You simply won't find the facilities you had within WP. The easiest way to replace /some/ of them is to adapt, and there are various ways to do that. However, it isn't particularly easy to work around some of Word's limitations, e.g. trying to use multiple data sources in a merge. Using SQL to modify your data source is one way around some of the problems. There are also plenty of fancy tricks you can do with fields, but in the end there's either a fancy trick that can be used to achieve a particular effect, or solve a particular problem, or there isn't, in which case you obviously have to fnd a different approach. For Field tips n tricks, useful sources are probably material by Cindy Meister, Doug Robbins, Graham Mayor, Charles Kenyon, macropod and myself (including the stuff posted on Greg Maxey's site). However, I don't know of a really comprehensive list of such stuff, nor am I about to produce one in the next 5 minutes :-) In my view the only way you are likely to be able to produce macros that initiate merges that are even remotely similar to the stuff you did with WP is to learn to use VBA (or VB.NET or whatever) to do the same thing. If you want to get info. from users, you either use Word ASK/FILLIN fields over which you will have little control, , or you use VBA-level facilities such as VBA Userforms which unfortunately require you to dive headlong into VBA, or simpler facilities such as VBA InputBox facilities. WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever to the variable vTxt (in this case, a field), and string commands, so then you can search for, say, the position of the first space with a string position command like STRPOS(" ";vTxt) and combine it with a substring command to subtract the bit you want and assign that to your variable. You just don't get this in the Word field system. Ways to achieve similar things a a. manipulate the data source b. use VBA and Word MailMerge Events to get at the data in your data source before it gets stuffed into the mail merge main document c. use a DATABASE field to extract info. from a mailmerge field. Unfortunately, this can only be sed if you can guarantee that your data does not have characters such as single-quotes in it, and MS has done something in recent version sof Word that makes this much more unreliable. Is there no way to invoke VBA? You know, somehow assign that field with the name to a variable, then invoke VBA to manipulate it, then insert the modified variable into the document? Again, I've done that with fields where I get want the user to be able to modify the info if need be, but not necessarily. [The routine is: 1. assign field to variable.. 2. invoke macro with prompt where variable pre-fills the prompt. 3. user can modify or not the variable. 4. macro stops, variable is inserted into text in WP's merge language.] Yes, use Word events. Have a look at Cindy Meister's website or look for articles in this group (e.g. via groups.google.com). But you can't insert the modified field using a MERGEFIELD field: you either have to stuff the modified text into the document or update a Document variable or Document property which is then inserted during the merge using a { DOCVAROABLE } field or { DOCPROPERTY } field. My best shot for now... Peter Jamieson "fscodave" wrote in message oups.com... That's /exactly/ the type of text manipulation I've been able to do in WP in its merge language and have failed to figure out how to do in Word. At least now I know not to waste my time. I had a routine to reduce the full name of an insurance company to a short name. (Like /Dominion of Canada/ to just /Dominion/.) And no, I can't modify the merge field: I just get a .dbf file straight from IT. I've been trying to figure out some way around Word's limitations. I posted a message a while ago, and got a somewhat unhelpful answer to forget what I could do in WP and move on to learn SQL or something. WP has merge commands like ASSIGN(vTxt;FIELD(Txt)), to assign whatever to the variable vTxt (in this case, a field), and string commands, so then you can search for, say, the position of the first space with a string position command like STRPOS(" ";vTxt) and combine it with a substring command to subtract the bit you want and assign that to your variable. Sigh. Is there no way to invoke VBA? You know, somehow assign that field with the name to a variable, then invoke VBA to manipulate it, then insert the modified variable into the document? Again, I've done that with fields where I get want the user to be able to modify the info if need be, but not necessarily. [The routine is: 1. assign field to variable.. 2. invoke macro with prompt where variable pre-fills the prompt. 3. user can modify or not the variable. 4. macro stops, variable is inserted into text in WP's merge language.] But so far I haven't found the equivalent of WP's VARIABLE(vTxt) command to insert the modified-or-not variable into the text... David Evans Peter Jamieson wrote: I would go Graham's route, but if you cannot do that, you can probably have 26 IF fields that do e,.g. { IF "{ MERGEFIELD Lead_PersonFirstname }" = "A*" "A" "" }{ IF "{ MERGEFIELD Lead_PersonFirstname }" = "B*" "B" "" } and so on... Peter Jamieson "Mark" wrote in message ... Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
Peter Jamieson wrote: If that message was from me, it would not have been posted without thought. Sorry. I'm obviously still grumpy about this change being forced on us from a sturdy system I designed 6 or 7 years ago. David |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
For he sake of the other participants, I suggest we don't start a grumpiness
contest - we seem too evenly matched :-) Peter Jamieson "fscodave" wrote in message ps.com... Peter Jamieson wrote: If that message was from me, it would not have been posted without thought. Sorry. I'm obviously still grumpy about this change being forced on us from a sturdy system I designed 6 or 7 years ago. David |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
I am a complete tyro, but I did get an answer in a new message I
posted. This was the answer from Cindy Meister: n the mail merge FAQ on my website you'll find a section on Word 2002/2003, and in there a discussion about using the mail merge events. There's a sample file that demonstrates basically what you need to do (manipulate the value of the merge field and put it in the document) http://homepage.swissonline.ch/cindy...002/MM2002.htm Cindy Meister [end of quote] Basically, you download a zip file after clicking on "mail merge events" from the page she points you to. The zip file has two files, both of which have to be unzipped to the same folder. MergeEvents.Doc is the main document, with some macros inside of it. I don't understand most of the code, which is in VBA, but I've figured out this much: the merge points to the other document in the zip file, employees.doc, which has the field FirstName. The guts of the relevant macro is a function GetInitial, and the most relevant line is this: szIntial = Left(doc.MailMerge.DataSource.DataFields("FirstNam e").Value, 1) & "." [typo with the "intial" instead of "initial" is there, but doesn't affect the function] Earlier in the macro the datasource had been defined, I guess, and you can see that it refers to the "FirstName" field, and takes the leftmost element of that field and adds a period to it. However, when I run the merge, I only get the first two records to merge in the resulting new document. Plus, I don't understand most of the rest of the macro. I gather that the initial is then put into the relevant bookmark. So it's possible to do what you asked, I think, but we'll have to brush up on the VBA to make it work! David Mark wrote: Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mailmerge - Firstname truncate to Initial?...
In another message I alluded to the fact that you can sometimes use DATABASE
fields to do this kind of stuff. It's kludgy and nasty, but it may work. First, create a blank file (e.g. using Notepad) called t.udl. Let's say it's in a folder called c:\a. Then insert the following DATABASE field { DATABASE \d "C:\\a\\t.udl" \c "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\a\\;Jet OLEDB:Engine Type=96;" \s "SELECT left('{ MERGEFIELD FirstName }',1)" } Make sure all the {} are the special field code braces you can insert using ctrl-F9. Don't use the \h switch (which adds a header) to the field. When the resulting table only contains one row/column Word does not wrap itin a table. Unfortunately, Word seems now to add a paragraph mark before the result sometimes. It doesn't seem to do this all the time, but it does seem to start doing it after a while for reasons I haven't been able to fathom. Also a. it will fail if the Firstname field contains a "'" character, and perhaps other awkward characters b. you may find one of Word's irritating security messages popping up. Once you have created the t.udl (which is just a "Data Link" file to fool Word into using its Jet SQL engine to do the SELECT) you shouldn't have to change any of the text in the DATABASE field except the SELECT statement. Because this uses the Jet dialect of SQL, you'll find that many, if not most of the functions available in Word VBA can also be applied to the operand - e.g. left, mid, right, instr, etc. Some things may have slightly different parameters than the functions available in Word, and some, such as replace, fail for no apparent reason. Peter Jamieson "fscodave" wrote in message oups.com... I am a complete tyro, but I did get an answer in a new message I posted. This was the answer from Cindy Meister: n the mail merge FAQ on my website you'll find a section on Word 2002/2003, and in there a discussion about using the mail merge events. There's a sample file that demonstrates basically what you need to do (manipulate the value of the merge field and put it in the document) http://homepage.swissonline.ch/cindy...002/MM2002.htm Cindy Meister [end of quote] Basically, you download a zip file after clicking on "mail merge events" from the page she points you to. The zip file has two files, both of which have to be unzipped to the same folder. MergeEvents.Doc is the main document, with some macros inside of it. I don't understand most of the code, which is in VBA, but I've figured out this much: the merge points to the other document in the zip file, employees.doc, which has the field FirstName. The guts of the relevant macro is a function GetInitial, and the most relevant line is this: szIntial = Left(doc.MailMerge.DataSource.DataFields("FirstNam e").Value, 1) & "." [typo with the "intial" instead of "initial" is there, but doesn't affect the function] Earlier in the macro the datasource had been defined, I guess, and you can see that it refers to the "FirstName" field, and takes the leftmost element of that field and adds a period to it. However, when I run the merge, I only get the first two records to merge in the resulting new document. Plus, I don't understand most of the rest of the macro. I gather that the initial is then put into the relevant bookmark. So it's possible to do what you asked, I think, but we'll have to brush up on the VBA to make it work! David Mark wrote: Hi all, I have a mail merge question, does anyone of you sage beings know how I can truncate Firstname (MERGEFIELD Lead_PersonFirstname) to its first initial? I.e. instead of showing Mark Ryan, showing M Ryan instead? Many thanks for your help! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indicate a mailmerge record number before launching Word? | Mailmerge | |||
Mailmerge and Access | Mailmerge | |||
Word Mailmerge with Access via DDE | Mailmerge | |||
Trying to suppress Select Table box in Excel-Word mailmerge | Mailmerge |