Prev: Capture user selection of font
Next: Word macro running from OL VBA throwing error on Word SaveAs??
From: Mark1110 on 1 Mar 2010 16:29
Hi,

I have a mail merge document that gets its information from an excel file.
Is there a function in word that will give me the length of the one of the
fields, i.e. mm_name? If not, is there a way to treat a phone number (555)
555-1212 as one word so the (555) is not on one line and 555-1212 on another.
The user won't delete the space between after the ) or will the user start
the phone number on a separate line.

I am currently adding spaces before the phone number and if the mm_name is
under 12 characters the letter looks okay, however if it is over 12
characters you something like this:

Jonathon Longname (555) 555-1212.

If not, is there a way when the letter is printed to delete all instances of
spaces that 2 or greater and just make them one space?

Here is the statement I tried but with no luck:

IF {len({MERGEFIELD FullName}+ {MERGEFIELD FirstName}) > 10 AND
len({MERGEFIELD FullName}+ {MERGEFIELD FirstName}) < 19 SPACE(19 -
len({MERGEFIELD FullName}+ {MERGEFIELD FirstName})}

I have never tried this in a word document. Am I using the correct syntax?


Thanks,

Mark
From: Doug Robbins - Word MVP on 1 Mar 2010 17:39
It is not possible to manipulate Word fields in that way (there is no Len
field function)

The only way that I could think of doing it is to use a "roll-your-own"
equivalent to mail merge using VBA

You will find a lot of the code that you would need in my response to the
post "Printing multiple labels based on contents of cell" in the
mailmerge.fields newsgroup to which you also posted this question.

Please do not post questions separately to multiple newsgroups. Rather,
insert the names of all of the newsgroups into the header of a single
message.

--
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, originally posted via msnews.microsoft.com

"Mark1110" <Mark1110(a)discussions.microsoft.com> wrote in message
news:0676DBFE-FD67-483D-A7CF-212F7D91FAD5(a)microsoft.com...
> Hi,
>
> I have a mail merge document that gets its information from an excel file.
> Is there a function in word that will give me the length of the one of the
> fields, i.e. mm_name? If not, is there a way to treat a phone number (555)
> 555-1212 as one word so the (555) is not on one line and 555-1212 on
> another.
> The user won't delete the space between after the ) or will the user start
> the phone number on a separate line.
>
> I am currently adding spaces before the phone number and if the mm_name is
> under 12 characters the letter looks okay, however if it is over 12
> characters you something like this:
>
> Jonathon Longname (555) 555-1212.
>
> If not, is there a way when the letter is printed to delete all instances
> of
> spaces that 2 or greater and just make them one space?
>
> Here is the statement I tried but with no luck:
>
> IF {len({MERGEFIELD FullName}+ {MERGEFIELD FirstName}) > 10 AND
> len({MERGEFIELD FullName}+ {MERGEFIELD FirstName}) < 19 SPACE(19 -
> len({MERGEFIELD FullName}+ {MERGEFIELD FirstName})}
>
> I have never tried this in a word document. Am I using the correct syntax?
>
>
> Thanks,
>
> Mark

From: Graham Mayor on 2 Mar 2010 01:53
Other possibilities include changing the Excel data to display numbers and
use a formatting switch c/w no breaking spaces and hyphens to format the
merge field
or
Merge to a new document and run the following macro on that document to
re-format the phone numbers with non breaking spaces and hyphens

Dim oRng As Range
Selection.HomeKey wdStory
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(findText:="\([0-9]{3}\) [0-9]{3}-[0-9]{4}", _
MatchWildcards:=True)
Set oRng = Selection.Range
oRng = Replace(oRng.Text, Chr(32), Chr(160))
oRng = Replace(oRng.Text, Chr(45), Chr(30))
Loop
End With

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


"Mark1110" <Mark1110(a)discussions.microsoft.com> wrote in message
news:0676DBFE-FD67-483D-A7CF-212F7D91FAD5(a)microsoft.com...
> Hi,
>
> I have a mail merge document that gets its information from an excel file.
> Is there a function in word that will give me the length of the one of the
> fields, i.e. mm_name? If not, is there a way to treat a phone number (555)
> 555-1212 as one word so the (555) is not on one line and 555-1212 on
> another.
> The user won't delete the space between after the ) or will the user start
> the phone number on a separate line.
>
> I am currently adding spaces before the phone number and if the mm_name is
> under 12 characters the letter looks okay, however if it is over 12
> characters you something like this:
>
> Jonathon Longname (555) 555-1212.
>
> If not, is there a way when the letter is printed to delete all instances
> of
> spaces that 2 or greater and just make them one space?
>
> Here is the statement I tried but with no luck:
>
> IF {len({MERGEFIELD FullName}+ {MERGEFIELD FirstName}) > 10 AND
> len({MERGEFIELD FullName}+ {MERGEFIELD FirstName}) < 19 SPACE(19 -
> len({MERGEFIELD FullName}+ {MERGEFIELD FirstName})}
>
> I have never tried this in a word document. Am I using the correct syntax?
>
>
> Thanks,
>
> Mark


From: Peter Jamieson on 2 Mar 2010 03:24
You can consider using either VBA and Word's Mail Merge Events, or using
VBA and an OpenDataSource call, to discover the length of mm_name.

The latter is more straightforward (as long as it works, of course): In
VBA, if it's Word 2002 or later, try running

Sub openmysource()
ActiveDocument.Mailmerge.OpenDataSource _
Name:="the full pathname of your Excel workbook", _
SQLStatement:= _
" SELECT x.*, len(x.mm_name) As [mmlen] " & _
" FROM [thesheetname] [x]"
End Sub

substituting the correct workbook pathname and the correct sheetname
(you will need "Sheet1$" for the standard "Sheet1", but you don't need
the $ for e.g. data source defined by range names.

Once you have this set up, Word should re-use the same query when it
re-opens the mail merge main document. But it isn't easy to do this if
the user may have to open different sheets to merge, or do their own
record filtering.

There's no particular reason why you could not concatenate mm_name, the
correct number of spaces, and the phone number into a single new column
using SQL, but you are limited to either 255 or 511 characters of SQL -
it should be enough to do that for a single column, but can be limiting
if there's other stuff you need to do in SQL. This approach will in any
case only work well if you are using a fixed-width font in the relevant
line of your output.

(Let's leave the Mail Merge Events approach for now).


Peter Jamieson

http://tips.pjmsn.me.uk

On 01/03/2010 21:29, Mark1110 wrote:
> Hi,
>
> I have a mail merge document that gets its information from an excel file.
> Is there a function in word that will give me the length of the one of the
> fields, i.e. mm_name? If not, is there a way to treat a phone number (555)
> 555-1212 as one word so the (555) is not on one line and 555-1212 on another.
> The user won't delete the space between after the ) or will the user start
> the phone number on a separate line.
>
> I am currently adding spaces before the phone number and if the mm_name is
> under 12 characters the letter looks okay, however if it is over 12
> characters you something like this:
>
> Jonathon Longname (555) 555-1212.
>
> If not, is there a way when the letter is printed to delete all instances of
> spaces that 2 or greater and just make them one space?
>
> Here is the statement I tried but with no luck:
>
> IF {len({MERGEFIELD FullName}+ {MERGEFIELD FirstName})> 10 AND
> len({MERGEFIELD FullName}+ {MERGEFIELD FirstName})< 19 SPACE(19 -
> len({MERGEFIELD FullName}+ {MERGEFIELD FirstName})}
>
> I have never tried this in a word document. Am I using the correct syntax?
>
>
> Thanks,
>
> Mark
 | 
Pages: 1
Prev: Capture user selection of font
Next: Word macro running from OL VBA throwing error on Word SaveAs??