Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numbers won't merge as formatted in Word 2007
In Tools menu, I checked the Option to "Confirm Conversion at Open". When I
select the spreadsheet I am given the option of selecting DDE, but the numbers still merge in unformatted and not at all like my spreadsheet. Any idea what else I can do to get this to work? Does Microsoft have a patch for this problem as it worked fine in older Word versions? The documents I have to merge are several pages long and contain quite a few fields to merge in, because of this I am looking for an alternative to formatting with switches. Thanks |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numbers won't merge as formatted in Word 2007
Adding formatting switches to the fields or formatting the source data as
text are the only options that you have. -- 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 "Cathy" wrote in message ... In Tools menu, I checked the Option to "Confirm Conversion at Open". When I select the spreadsheet I am given the option of selecting DDE, but the numbers still merge in unformatted and not at all like my spreadsheet. Any idea what else I can do to get this to work? Does Microsoft have a patch for this problem as it worked fine in older Word versions? The documents I have to merge are several pages long and contain quite a few fields to merge in, because of this I am looking for an alternative to formatting with switches. Thanks |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numbers won't merge as formatted in Word 2007
Your best bet is probably to copy/paste your Excel sheet into a new Word
document, then use that as the data source, unless you have multi-line data in your sheet and more columns than the maximum column count for a Word table. Incidentally (or perhaps not) is your Excel file a new-format ..xlsx/.xlsm or an old-format .xls? It may be worth trying the other format if you have not already done so. Peter Jamieson http://tips.pjmsn.me.uk Cathy wrote: In Tools menu, I checked the Option to "Confirm Conversion at Open". When I select the spreadsheet I am given the option of selecting DDE, but the numbers still merge in unformatted and not at all like my spreadsheet. Any idea what else I can do to get this to work? Does Microsoft have a patch for this problem as it worked fine in older Word versions? The documents I have to merge are several pages long and contain quite a few fields to merge in, because of this I am looking for an alternative to formatting with switches. Thanks |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numbers won't merge as formatted in Word 2007
That is what I was afraid you would say. Thanks for your help
"Doug Robbins - Word MVP" wrote: Adding formatting switches to the fields or formatting the source data as text are the only options that you have. -- 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 "Cathy" wrote in message ... In Tools menu, I checked the Option to "Confirm Conversion at Open". When I select the spreadsheet I am given the option of selecting DDE, but the numbers still merge in unformatted and not at all like my spreadsheet. Any idea what else I can do to get this to work? Does Microsoft have a patch for this problem as it worked fine in older Word versions? The documents I have to merge are several pages long and contain quite a few fields to merge in, because of this I am looking for an alternative to formatting with switches. Thanks |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Numbers won't merge as formatted in Word 2007
Hi Cathy,
You don't say what kind of data you're dealing with, so here's a fairly comprehensive list: Mailmerge Number & Currency Formatting In Word, add a numeric picture switch to the mergefield. To do this: .. select the field; .. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData}; .. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below); Note: The '\# 0' in the field is referred to as a numeric picture switch. Other possibilities include: .. \# 0 for rounded whole numbers .. \# ,0 for rounded whole numbers with a thousands separator .. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator .. \# $,0 for rounded whole dollars with a thousands separator .. \# $,0.00;($,0.00);'-' for currency, with brackets around negative numbers and a hyphen for 0 values The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'. If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00) zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s. Force $0.00 substitution for missing records You can force the substitution of $0.00 for missing records via the following process: 1. Select your mergefield. For the purposes of this example, I'll suppose it's named 'Value' and shows up as '«Value»' in your mailmerge main document. 2. Press Ctrl-F9 to embed your mergefield in another field, so that you get '{«Value»}'. 3. Type 'Set Val' into the '{«Value»}' field, so that you end up with '{Set Val «Value»}' 4. After the newly-inserted field, press Ctrl-F9 again to insert another (empty) field, so that you get '{Set Val «Value»}{ }' 5. Type '=Val \# "$,0.00"' into the second field, so that you end up with '{Set Val «Value»}{=Val \# "$,0.00"}' Mailmerge Percentage Formatting In Word, add a formula and numeric picture switch to the mergefield. To do this: .. select your mergefield, which will look something like «Percent»; .. press Ctrl-F9 to wrap another field around it, thus - { «Percent» }; .. edit the field so that you get {=«Percent»*100 \# 0.00%}; Note: the precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'. Mailmerge Phone Number Formatting In Word: For the phone numbers, all you need to do is to add a picture switch to the mergefield in you mailmerge template. To do this: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name; .. delete everything between 'MyData' and the closing field brace; .. add ' \# "(000) 0000 0000"' after 'MyData', so that you end up with {MERGEFIELD MyData \# "(000) 0000 0000"}; Mailmerge Date Formatting To get the date format you want, you can add a formatting picture switch. In Word: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include: . \@ "dddd, d MMMM yyyy"; . \@ "ddd, d MMMM yyyy"; . \@ "d MMM yyyy"; . \@ "dd/MMM/yyyy"; . \@ "d-MM-yy"; Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months. Mailmerge Time Formatting To get the time format you want, you can add a formatting picture switch. In Word: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyTime} where 'MyTime' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "h:m"', as in {MERGEFIELD MyTime \@ "h:m am/pm"}. Note: you must use lowercase 'm's for minutes; .. if you want the hours and/or minutes to display leading 0s, change 'h' to 'hh for hours and 'm' to 'mm' for minutes; When you're done making the changes to a given mergefield: .. position the cursor anywhere in this field and press F9 to update it; .. run your mailmerge. -- Cheers macropod [Microsoft MVP - Word] "Cathy" wrote in message ... In Tools menu, I checked the Option to "Confirm Conversion at Open". When I select the spreadsheet I am given the option of selecting DDE, but the numbers still merge in unformatted and not at all like my spreadsheet. Any idea what else I can do to get this to work? Does Microsoft have a patch for this problem as it worked fine in older Word versions? The documents I have to merge are several pages long and contain quite a few fields to merge in, because of this I am looking for an alternative to formatting with switches. Thanks |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you keep a formatted cell from changing in a word merge? | Mailmerge | |||
why previously formatted and used disc showing not formatted | Microsoft Word Help | |||
how to get excel formatted date in word during mail merge | Mailmerge | |||
Email merge that sends out in formatted text | Mailmerge | |||
Mail Merge does not accept formatted cell of Excel | Mailmerge |