Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
How do I keep the display result in Mail Merge to follow exactly the format
set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Try setting the "Confirm file coversions at open" item under
ToolsOptionsGeneral and then when you attach the data source, use the DDE option. You may also need to format the cells in column that contains that data as Text. -- 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 "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Hi Mike,
The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Hi Doug,
The DDE options worked. The formating was retained. Thanks for your tips. I have another problem though. All the while, I've been testing the merge documents stored in local hard drive. It works fine. However, when I tried to merged documents located in folders stored in the server, as I clicked open the Word document, it gave me an error that is can't locate the source data. For your info, my source data is a ms query data in excel format. I then tried stored the query file in my local harddrive while maintaining the Word file in the server, it works. Does it means that my source data must always be in the server or there is a way to get around it? Thanks in advance. Regards. Mike "Doug Robbins - Word MVP" wrote: Try setting the "Confirm file coversions at open" item under ToolsOptionsGeneral and then when you attach the data source, use the DDE option. You may also need to format the cells in column that contains that data as Text. -- 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 "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Dear macropod,
Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Normally Word will retain information about the attached data source. By
default it expects to find that data source in the My Data Sources folder or in the same folder as the merge document. Elsewhere and it *may* not be able to keep track. The answers are to re-attach the data source before merging or move it to a location where it can find it. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Doug, The DDE options worked. The formating was retained. Thanks for your tips. I have another problem though. All the while, I've been testing the merge documents stored in local hard drive. It works fine. However, when I tried to merged documents located in folders stored in the server, as I clicked open the Word document, it gave me an error that is can't locate the source data. For your info, my source data is a ms query data in excel format. I then tried stored the query file in my local harddrive while maintaining the Word file in the server, it works. Does it means that my source data must always be in the server or there is a way to get around it? Thanks in advance. Regards. Mike "Doug Robbins - Word MVP" wrote: Try setting the "Confirm file coversions at open" item under ToolsOptionsGeneral and then when you attach the data source, use the DDE option. You may also need to format the cells in column that contains that data as Text. -- 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 "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
DDE is at best a flaky method of connection to a data source which is the
reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Hi Graham,
I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
{ IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }"
"{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Hi Graham,
Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
In that case you would need to nest the further condition in the 'false'
side of the first conditional field { IF{ Mergefield Field2 \*Upper } = "H" "{ Mergefield Field1 \# ",0.0" }" "{ IF{ Mergefield Field2 \*Upper} = "D" "{ Mergefield Field1 \@ "d-MM-yy"}" "{ Mergefield Field1 \# ",0" }" }"} It works OK with my Excel test workbook based on your data sample. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Hi Mike,
Try: {IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"} {IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@ "dd-MMM-yy"} {Mergefield Field1 \# ",0"}}} -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
I appreciate quotes are not necessarily required with fields, as in your
example, but I find it helpful to include them with conditional constructions, if only so you can see at a glance which bit of the total field belongs with what -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Mike, Try: {IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"} {IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@ "dd-MMM-yy"} {Mergefield Field1 \# ",0"}}} "Mike" wrote in message ... Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Fair enough - each to their own. I eschew the unnecessary clutter.
-- Cheers macropod [MVP - Microsoft Word] "Graham Mayor" wrote in message ... I appreciate quotes are not necessarily required with fields, as in your example, but I find it helpful to include them with conditional constructions, if only so you can see at a glance which bit of the total field belongs with what -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Mike, Try: {IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"} {IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@ "dd-MMM-yy"} {Mergefield Field1 \# ",0"}}} "Mike" wrote in message ... Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#15
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Hi Graham,
Your syntax work for my application. Thanks for your help. Have a good weekend. Best regards, Mike "Graham Mayor" wrote: In that case you would need to nest the further condition in the 'false' side of the first conditional field { IF{ Mergefield Field2 \*Upper } = "H" "{ Mergefield Field1 \# ",0.0" }" "{ IF{ Mergefield Field2 \*Upper} = "D" "{ Mergefield Field1 \@ "d-MM-yy"}" "{ Mergefield Field1 \# ",0" }" }"} It works OK with my Excel test workbook based on your data sample. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#16
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
Dear macropod,
Your syntax also work. Appreciate the help given by both of you. HAve good weekend. Best regards, Mike "macropod" wrote: Hi Mike, Try: {IF{MERGEFIELD Field2 \*Upper}= "H" {Mergefield Field1 \# ",0.0"} {IF{MERGEFIELD Field2 \*Upper}= "D" {Mergefield Field1 \@ "dd-MMM-yy"} {Mergefield Field1 \# ",0"}}} -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
#17
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Problem Maintaing Excel Format after Mail Merge
You are welcome
-- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, Your syntax work for my application. Thanks for your help. Have a good weekend. Best regards, Mike "Graham Mayor" wrote: In that case you would need to nest the further condition in the 'false' side of the first conditional field { IF{ Mergefield Field2 \*Upper } = "H" "{ Mergefield Field1 \# ",0.0" }" "{ IF{ Mergefield Field2 \*Upper} = "D" "{ Mergefield Field1 \@ "d-MM-yy"}" "{ Mergefield Field1 \# ",0" }" }"} It works OK with my Excel test workbook based on your data sample. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, Thanks for your sample syntax. It worked. I have one more question on this matter though. The syntax worked when the IF testing contains only 2 criteria, like the problem in my first example, if =H, then it is "0.0" and all others "0". However, I got one more field that requires more than one testing criteria, as follow: Field 1 Field 2 18-Aug-08 D 2.1 H 2 U Now, my test condition require IF Field 2=D, then Field 1 format must be date, and IF Field 2=H, then the format must be "0.0" and the rest will be "0.0". Hope you can give me a clue how to write the syntax for such condition. Thanks and regards, Mike "Graham Mayor" wrote: { IF { MERGEFIELD Field2 \*Upper} = "H" "{ Mergefield Field1 \# ",0.0" }" "{ Mergefield Field1 \# ",0" }"} The comma in the switches is the (optional) thousands separator. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Hi Graham, I have this perculiar requirement that after merging the document, I need the Field 1 decimal point to be as such that when Field 2=H, the decimal format should be "0.0", and no decimal point for anything other than H in Field 2. Can you suggest a conditional formating formula that I should use in this case? Field 1 Field 2 3 D 2.3 H 4 U 5 C Regards, Mike "Graham Mayor" wrote: DDE is at best a flaky method of connection to a data source which is the reason it was abandoned as the default from Word 2002. Ultimately it would be better to configure your data source properly to avoid the problem in the first place, or to use conditional fields to ensure that the correct data is applied - see also http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Mike wrote: Dear macropod, Haven't try this yet, but tried the DDE method suggested by Doug Robbins. It worked, but have some problems if the files are stored in the server. Anyway, thanks for your suggestion. Regards, Mike "macropod" wrote: Hi Mike, The fact that there are different kinds of data in the fields doesn't prevent formatting switches being used. At most you'll need an IF test. For example: {IF{MERGEFIELD Data \@ "dd-MM-yy"}= "??-??-??" {MERGEFIELD Data \@ "dd-MM-yy"} {MERGEFIELD Data \# ",0.00"}} Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. -- Cheers macropod [MVP - Microsoft Word] "Mike" wrote in message ... How do I keep the display result in Mail Merge to follow exactly the format set in the Excel file? I cannot use the format switches due to the same field in the excel file contains multiple format, ie. the same field contains data like date and numbers, i need the display result in Word to show both dates and numbers. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge Format Problem | Microsoft Word Help | |||
Excel & Mail Merge problem - help?? | Mailmerge | |||
Excel & Mail Merge Problem - help! | Mailmerge | |||
Insert Merge Field problem with Word-Mail Merge from Excel documen | Microsoft Word Help | |||
mail merge from excel that changes my format from UK to US format | Mailmerge |