Zero in merge document when blank field in Excel
Previous suggestion for this problem was : "Make sure the connection to the data source uses a method other than what it is now using. I think you want DDE"
How do we do this? Can't find an option in Word XP Mail Merge for DDE.
In Word 97/2000 there used to be an option to not print blank fields when data source is empty. Can;t find s#uch an option in Word XP.
Something as basic as merging an address does not seem as easy in Office XP?
Using Office XP Word & Excel.
thanks
How do we do this? Can't find an option in Word XP Mail Merge for DDE.
In Word 97/2000 there used to be an option to not print blank fields when data source is empty. Can;t find s#uch an option in Word XP.
Something as basic as merging an address does not seem as easy in Office XP?
Using Office XP Word & Excel.
thanks
WileECoyote45305
It seems to automatically "close up" the blank lines now, but I can't find the option to make it keep the blank lines when I have a blank field. I have been wondering about this myself but can't seem to find the setting, if it exists anymore.
ASKER
I do want the blank lines closed up! - at present they are staying there and a zero is being put in. i.e. it is not automatically closing them up.
The option to suppress blank lines is still there, it's just hidden, well hidden! Thankyou Microsoft!
By default it should be set but it appears that you have got it unset somehow. You should be able to reset it via code, or you can add the the old "Merge..." button to your Mail Merge toolbar and get the choice back.
Make sure you have the Mail Merge Toolbar visible
Go to Tools > Customize > Commands tab
Scroll down the Categories list and select "All Commands"
Scroll down the Commands list and find "MailMerge" (it's a fair way down but they are mostly in alphabetic order)
Click and Drag "MailMerge" to your Mail Merge Toolbar and the old icon will appear.
Close the customize dialog
Use the old button to get your option.
By default it should be set but it appears that you have got it unset somehow. You should be able to reset it via code, or you can add the the old "Merge..." button to your Mail Merge toolbar and get the choice back.
Make sure you have the Mail Merge Toolbar visible
Go to Tools > Customize > Commands tab
Scroll down the Categories list and select "All Commands"
Scroll down the Commands list and find "MailMerge" (it's a fair way down but they are mostly in alphabetic order)
Click and Drag "MailMerge" to your Mail Merge Toolbar and the old icon will appear.
Close the customize dialog
Use the old button to get your option.
Thank you Tony!!! And thanks to rutlandict for asking a question which has been bothering me (but not too much to make me ask here, as I haven't actually had to do a merge in awhile - I just noticed the blank line suppression option was missing...)
ASKER
Under Tools-Customize-Commands - There isn't a command to drag called "Mailmerge"?
There is a column called categories (which has MailMerge in it but you can't drag that) and a column called commands (which can be dragged to the toolbar) but I can't see which button would allow you to suppress blank lines.
Here's the choice of commands under the category MailMerge:
Mail Merge Wizard
Show Mail Merge toolbar
Insert Merge Field
Insert Word Field
Show Fields
Highlight Fields
Data Form
Merge to document
Merge to printer
Error check
First
Previous
Record
Next
Last
Find in Field
Data Form
Manage Fields
Add Record
Delete Record
What do you mean by "the old button"?
There is a column called categories (which has MailMerge in it but you can't drag that) and a column called commands (which can be dragged to the toolbar) but I can't see which button would allow you to suppress blank lines.
Here's the choice of commands under the category MailMerge:
Mail Merge Wizard
Show Mail Merge toolbar
Insert Merge Field
Insert Word Field
Show Fields
Highlight Fields
Data Form
Merge to document
Merge to printer
Error check
First
Previous
Record
Next
Last
Find in Field
Data Form
Manage Fields
Add Record
Delete Record
What do you mean by "the old button"?
Do not choose MailMerge from the category list (on the left) - choose "All Commands"
Then you will find "MailMerge" in the list of Commands (on the right)
When you have dragged this to the Toolbar you will have a button labelled "Merge..."
Click on this to do your merge (instead of the new "merge to new document" icon) and you will get the old dialog (from Word 2K) and will have the raio button options to suppress (or not) blank lines.
Then you will find "MailMerge" in the list of Commands (on the right)
When you have dragged this to the Toolbar you will have a button labelled "Merge..."
Click on this to do your merge (instead of the new "merge to new document" icon) and you will get the old dialog (from Word 2K) and will have the raio button options to suppress (or not) blank lines.
ASKER
ok this solution means word does not print a blank line when the data field is empty but am still getting zeros.
These zeros are visible in Excel (if you choose to display zero values) due to a VLOOKUP i.e. they are not present in the cell VLOOKUP gets its data from but because there is no data there, the result achieved by VLOOKUP seems to be a zero by default.
This zero then ends up in the merged doc! (the merge field is the cell with the VLOOKUP formula in it).
These zeros are visible in Excel (if you choose to display zero values) due to a VLOOKUP i.e. they are not present in the cell VLOOKUP gets its data from but because there is no data there, the result achieved by VLOOKUP seems to be a zero by default.
This zero then ends up in the merged doc! (the merge field is the cell with the VLOOKUP formula in it).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Make a copy of your Excel file.
Try saving the Excel file as a Text (.TXT) file.
Then save that Text file as an Excel file again.
Now try again to do your mail merge using your most recent Excel file.
Try saving the Excel file as a Text (.TXT) file.
Then save that Text file as an Excel file again.
Now try again to do your mail merge using your most recent Excel file.