Deleting a blank line in mail merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anjw38 - 09 Jan 2008 16:50 GMT

I know I've seen the answer to this before the first time I researched it,
but can't seem to find it now. It was a macro.

I am doing a document for several customers with personal information being
imported for each customer. Each customer has a different amount of
suggested services. How do I get the blank suggested services to delete from
the document?

Reply to this Message

Doug Robbins - Word MVP - 09 Jan 2008 19:55 GMT

Sounds like you are probably trying to perform a "multiple items per
condition (=key field)" mailmerge which Word does not really have the
ability to do:

See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic

Or take a look at the following Knowledge Base Article

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303

or at:

http://cornell.veplan.net/article.aspx?&a=3815

Signature

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

>I know I've seen the answer to this before the first time I researched it,
> but can't seem to find it now. It was a macro.
[quoted text clipped - 5 lines]
> from
> the document?

Reply to this Message

anjw38 - 09 Jan 2008 20:52 GMT

I am not sure I understand what you mean. Let me explain a little more.

We are sending out a letter to each of our customers. Each customer has
recommended services that should be preformed in the coming year. Each
customer has a different amount of recommended services. ie. some have 6
some have 3 some have 15. The doc is set up for the max (15). Is there a
way of removing the extra 12 lines if a customer only has 3 recommended
services on their proposal? All the information is being merged in from an
excel worksheet. I am working in Office 2003. Thanks for any help you can
provide. I'm pretty literate but if you use too many big words I go on
temporary vacation. :-)

> I know I've seen the answer to this before the first time I researched it,
> but can't seem to find it now. It was a macro.
[quoted text clipped - 3 lines]
> suggested services. How do I get the blank suggested services to delete from
> the document?

Reply to this Message

anjw38 - 09 Jan 2008 21:59 GMT

Here is an example of what the doc looks like. Now if there is no
information to fill in the next work code isn't there an IF that can be
written or something like that
«proposaldetail_work_code»-«proposaldetail_description» «workcode_description»
«proposaldetail_price»

«proposaldetail_what»
«proposaldetail_work_code2» «proposaldetail_description2»
«workcode_description2»
«proposaldetail_price2»

«proposaldetail_what2»
«proposaldetail_work_code3» «proposaldetail_description3»
«workcode_description3»
«proposaldetail_price3»

«proposaldetail_what3»
«proposaldetail_work_code4» «proposaldetail_description4»
«workcode_description4»
«proposaldetail_price4»

«proposaldetail_what4»
«proposaldetail_work_code5» «proposaldetail_description5»
«workcode_description5»
«proposaldetail_price5»

«proposaldetail_what5»
«proposaldetail_work_code6» «proposaldetail_description6»
«workcode_description6»
«proposaldetail_price6»

etc etc up to code15

Thank you anyone for any help. I feel like I'm right on the verge of
solving this, but just need that little extra piece of info.

> I am not sure I understand what you mean. Let me explain a little more.
>
[quoted text clipped - 15 lines]
> > suggested services. How do I get the blank suggested services to delete from
> > the document?

Reply to this Message

Peter Jamieson - 10 Jan 2008 15:33 GMT

> information to fill in the next work code isn't there an IF that can be
> written or something like that

If all your data is coming from the same record in the data source, what you
need is something like

{ IF "{ MERGEFIELD proposaldetail_work_code2 }" = "xxxxx" ""
"{ MERGEFIELD proposaldetail_what }
{ MERGEFIELD proposaldetail_work_code2 } { MERGEFIELD
proposaldetail_description2 }
{ MERGEFIELD workcode_description2 }
{ MERGEFIELD proposaldetail_price2 }" }

and the same for all the other work codes. I'm assuming there is a value
"xxxxx" that the Work code has when you do not have a work code. Perhaps it
is "".

{ MERGEFIELD proposaldetail_what } is the same thing as
«proposaldetail_work_code2» except with the "underlying field codes
displayed" e.g. using Alt-F9, and the {} are all the special "field code
braces" that you can enter using ctrl-F9

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Here is an example of what the doc looks like. Now if there is no
> information to fill in the next work code isn't there an IF that can be
[quoted text clipped - 58 lines]
>> > delete from
>> > the document?

Reply to this Message

anjw38 - 10 Jan 2008 16:49 GMT

Thank you. It looks a little Greek to me, but I think we can figure it out.
Just to make sure that you have all the information and are responding
correctly. The information is all coming from an Excel spreadsheet which is
coming from our customer data base. When there is not a work code for that
customer the cell in Excel is empty. I hope I am not being a complete ditz
about this. We sure do appreciate all the help from you guys.

> > information to fill in the next work code isn't there an IF that can be
> > written or something like that
[quoted text clipped - 80 lines]
> >> > delete from
> >> > the document?

Reply to this Message

Peter Jamieson - 10 Jan 2008 18:24 GMT

> I hope I am not being a complete ditz
> about this.

I doubt it. Things should become clearer after you work with the fields in
the Word document for a minute or two (I advise you to enable Word's
MailMerge toolbar) and use e.g. alt-F9 to toggle between "field code
display" and "field result display", switch preview on and off, use ctrl-F9
to enter some of those special field code braces, and copy/paste a couple of
field codes.

> Just to make sure that you have all the information and are responding
> correctly. The information is all coming from an Excel spreadsheet which
> is
> coming from our customer data base.

As long as the data is in the Excel sheet when Word connects to it, you
should be OK. If you are using External database links in Excel to retrieve
data, don't expect those links to be re-executed when Word connects to the
data source. They won't be (if you need to do that, you probably need to
connect from Word directly to your customer data base).

> When there is not a work code for that
> customer the cell in Excel is empty.

If the codes are numeric, it is possible that they will appear in Word as 0,
in which case you may need to test against "0" rather than "".
Unfortunately, this depends on the mix of numeric and "text" results in the
first 8 rows of the column, but let's hope it's an alphanumeric code in this
case.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Thank you. It looks a little Greek to me, but I think we can figure it
> out.
[quoted text clipped - 104 lines]
>> >> > delete from
>> >> > the document?

Reply to this Message