Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Blank fields in Excel become 0 in MailMerge data file
The field is blank in Excel (Clear Contents) and whether formatted as General
or Text it converts to 0 (and prints as such) when the file is linked in MailMerge. I need it to be blank and then skipped when printing the merged document. HELP! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Blank fields in Excel become 0 in MailMerge data file
In the mailmerge main document, use the following If...then...Else field
construction: { IF { MERGEFIELD fieldthatmightbeblankname } = 0 " " { MERGEFIELD fieldthatmightbeblankname } } You must use Ctrl+F9 to insert each pair of field delimiters and Alt+F9 to toggle off their display. -- 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 "edenner" wrote in message ... The field is blank in Excel (Clear Contents) and whether formatted as General or Text it converts to 0 (and prints as such) when the file is linked in MailMerge. I need it to be blank and then skipped when printing the merged document. HELP! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Blank fields in Excel become 0 in MailMerge data file
"Doug Robbins - Word MVP" wrote: In the mailmerge main document, use the following If...then...Else field construction: { IF { MERGEFIELD fieldthatmightbeblankname } = 0 " " { MERGEFIELD fieldthatmightbeblankname } } You must use Ctrl+F9 to insert each pair of field delimiters and Alt+F9 to toggle off their display. -- 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 "edenner" wrote in message ... The field is blank in Excel (Clear Contents) and whether formatted as General or Text it converts to 0 (and prints as such) when the file is linked in MailMerge. I need it to be blank and then skipped when printing the merged document. HELP! This is not very workable. I have the same problem as the original poster (OP), and my mail merge document is very large and already contains many conditional fields. The grade reports for our school are prepared as a mail merge document with the data maintained in MS Excel spreadsheets. In some fields the student has an elective option, so I use IF..THEN..ELSE Word fields to contain the choices of different possible data base fields. This setup worked as expected in Word 2.0, Word 3.0, Word 5.0, Office 95, Office 98, and Office 2000. The behavior is broken in Office 2003. I have thought of trying to insert a numerical picture at the end of the field statement, but this is making already complex statements even harder to understand. As I recall, the picture I added was something like "\# ####;'';''", using paired single quotes to represent the null string. I would really appreciate any suggestions for a simpler solution. I have work-arounds, but they are all irritating. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Blank fields in Excel become 0 in MailMerge data file
The behaviour has not 'broken' it was deliberately changed to overcome
problems related to the rather flaky DDE data connection method that was used in the earlier versions. Current merges (from Word 2002 on) collect only the raw data from the data source and formatting with switches is the best and most reliable way of dealing with formatting issues that arise. http://www.gmayor.com/formatting_word_fields.htm The DDE method of data connection is however still available in the application. From the Tools menu in Word, select Options and then go to the General tab and check the box against the "Confirm conversions at open" item. (In Word 2007 it is Office Button Word Options Advanced General Confirm file format conversion on open). Then when you attach the data source to the mail merge main document, you will be given the option of using the DDE method of connection which should read the data as you have it formatted in the table. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Acharn wrote: "Doug Robbins - Word MVP" wrote: In the mailmerge main document, use the following If...then...Else field construction: { IF { MERGEFIELD fieldthatmightbeblankname } = 0 " " { MERGEFIELD fieldthatmightbeblankname } } You must use Ctrl+F9 to insert each pair of field delimiters and Alt+F9 to toggle off their display. -- 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 "edenner" wrote in message ... The field is blank in Excel (Clear Contents) and whether formatted as General or Text it converts to 0 (and prints as such) when the file is linked in MailMerge. I need it to be blank and then skipped when printing the merged document. HELP! This is not very workable. I have the same problem as the original poster (OP), and my mail merge document is very large and already contains many conditional fields. The grade reports for our school are prepared as a mail merge document with the data maintained in MS Excel spreadsheets. In some fields the student has an elective option, so I use IF..THEN..ELSE Word fields to contain the choices of different possible data base fields. This setup worked as expected in Word 2.0, Word 3.0, Word 5.0, Office 95, Office 98, and Office 2000. The behavior is broken in Office 2003. I have thought of trying to insert a numerical picture at the end of the field statement, but this is making already complex statements even harder to understand. As I recall, the picture I added was something like "\# ####;'';''", using paired single quotes to represent the null string. I would really appreciate any suggestions for a simpler solution. I have work-arounds, but they are all irritating. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Blank fields in Excel become 0 in MailMerge data file
I hate being forced to "top post".
Anyway, thanks for this very helpful answer. The referenced article has some *very* good stuff, and I'm grateful to find out the reason for the change in behavior. The developers may not consider this "broken", but I had never seen any mention of the change and was taken by surprise. I spent a couple of days floundering until I remembered the possibility of using a display switch. Incidentally, the numeric switch I mentioned, "\# ####;'';''", displays alphabetic characters as well as numbers, if the data field is not blank, and suppresses the 0. "Graham Mayor" wrote: The behaviour has not 'broken' it was deliberately changed to overcome problems related to the rather flaky DDE data connection method that was used in the earlier versions. Current merges (from Word 2002 on) collect only the raw data from the data source and formatting with switches is the best and most reliable way of dealing with formatting issues that arise. http://www.gmayor.com/formatting_word_fields.htm The DDE method of data connection is however still available in the application. From the Tools menu in Word, select Options and then go to the General tab and check the box against the "Confirm conversions at open" item. (In Word 2007 it is Office Button Word Options Advanced General Confirm file format conversion on open). Then when you attach the data source to the mail merge main document, you will be given the option of using the DDE method of connection which should read the data as you have it formatted in the table. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Blank fields in Excel become 0 in MailMerge data file
Acharn wrote:
I hate being forced to "top post". No one is forcing you to do anything. Top or bottom posting is a choice, but top posting is more convenient to read as the current text is at the top - and is the default for Outlook Express which most of the regular contributors to this forum use. Anyway, thanks for this very helpful answer. The referenced article has some *very* good stuff, and I'm grateful to find out the reason for the change in behavior. The developers may not consider this "broken", but I had never seen any mention of the change and was taken by surprise. I spent a couple of days floundering until I remembered the possibility of using a display switch. The function was changed in Word 2002 so has been around a long time. Incidentally, the numeric switch I mentioned, "\# ####;'';''", displays alphabetic characters as well as numbers, if the data field is not blank, and suppresses the 0. It is the latter part of the switch after the second colon that suppresses the 0. The hashes as you have them make no account of thousands separators and the first quote should go between \# and # thus \# "# if you wish to use this construct. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word Mailmerge using Excel Data File | Mailmerge | |||
Correct spacing for blank fields in name mailmerge | Mailmerge | |||
Why will mailmerge not use all the fields from data source? | Mailmerge | |||
Mailmerge:unwanted blank lines caused by empty data fields | Microsoft Word Help | |||
blank fields in merg data file | Mailmerge |