Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / October 2004

Tip: Looking for answers? Try searching our database.

How to eliminate blank lines when mail merge has no data for an i.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Lynnt - 07 Oct 2004 18:47 GMT
In our mailmerge, we have several currency amounts tied to a user, all in an
Excel spreadsheet.  When we merge the Word and Excel data, the currency
formatting is lost.  So I used the Dynamic Data Exchange method, and I tried
the format field codes methods, but they didn't quite do the trick.  Here's
the situation:

Excel:
Customer    Dinner    Shoes     Table         Car
Joe Blow     $5.00     $4,000     $0             $30,000

The mailmerge result:
Customer    Joe Blow
Dinner         5.0
Shoes         4000.0
Table          0
Car             30000.0

What we'd like is:
Customer    Joe Blow
Dinner        $5
Shoes        $4,000
Car            $30,000

Not that when there is no amount, the line item does not appear.

How do we do this?  We used to be able to do it, but now it doesn't seem to
work in Word 2003.  Thanks much for your help!
Andrea Martin - 07 Oct 2004 19:48 GMT
To format currency, click on Help and type in  "Numeric picture switches"
for instructions and examples.

> In our mailmerge, we have several currency amounts tied to a user, all in an
> Excel spreadsheet.  When we merge the Word and Excel data, the currency
[quoted text clipped - 23 lines]
> How do we do this?  We used to be able to do it, but now it doesn't seem to
> work in Word 2003.  Thanks much for your help!
macropod - 07 Oct 2004 21:30 GMT
Hi Lynn,

What you need to do is to test each MERGEFIELD's contents for a value >0,
and add a numeric picture switch to the displayed value, thus:
{IF{MERGEFIELD ItemTypeCost}> 0 "{MERGEFIELD ItemType}[tab]{MERGEFIELD
ItemTypeCost \# $,0.00}[cr]" ""}
where the [tab] and [cr] represent an actual tab and carriage return (or
line-feed), respectively. When doing this, you don't put carriage returns or
line-feeds between the different sets of MERGEFIELDs, except for after the
customer name.

Oh, and how to do this? Select the MERGEFIELDs and press Shift-F9. That will
expose the field coding, which probably looks something like:
{MERGEFIELD ItemType}[tab]{MERGEFIELD ItemTypeCost}[cr]
Select a given set of these fields, tabs and carriage returns/line-feeds and
press Ctrl-F9 to put another field around them, thus:
{ {MERGEFIELD ItemType}[tab]{MERGEFIELD ItemTypeCost}[cr] }
Then put the cursor between the first two field braces (ie '{ {' ) and press
Ctrl-F9 again to insert another field, thus:
{ { } {MERGEFIELD ItemType}[tab]{MERGEFIELD ItemTypeCost}[cr] }
Now simply type the rest of the information in, being careful to replicate
the character spacing here (> 0 ") and here ([cr]" "") exactly.

When you're done, select the whole field and press Shift-F9 to update then
run your mailmerge.

Cheers
PS: You can't simply type the field braces - they must be created via
Ctrl-F9

> In our mailmerge, we have several currency amounts tied to a user, all in an
> Excel spreadsheet.  When we merge the Word and Excel data, the currency
[quoted text clipped - 23 lines]
> How do we do this?  We used to be able to do it, but now it doesn't seem to
> work in Word 2003.  Thanks much for your help!

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.