Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-07-2012, 05:36 AM
3N1GM4 3N1GM4 is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Novice
Directory Mail Merge with table layout
 
Join Date: Feb 2012
Posts: 6
3N1GM4 is on a distinguished road
Default Directory Mail Merge with table layout

Hi all,



I am trying to perform a Directory type Mail Merge from Excel into Word where the data being referenced in Excel has repeated values in one column which I effectively want to group on so that I produce one document per value in that column instead of one column per record in the range.

The Directory Mail Merge option seems to work perfectly for this and by following some instructions online I have been able to successfully get the merge to work.

However, I have one final layout issue which is bugging me and I can't seem to get it to work properly. If I give a simplified example of what I am doing, perhaps this will make more sense.

Say I have the following data in my Excel sheet in the range which is being specified in my Mail Merge:

Code:
Customer    Site             Item     Charge1   Charge2   Charge3
--------    ----             ----     -------   -------   -------
Customer 1  Little Place     Widget    45.00     75.00     95.00  
Customer 1  Medium Road      Widget    40.00     70.00     98.00  
Customer 1  Medium Road      Thing     48.00     76.00     92.00  
Customer B  Red Street       Widget    35.00     72.00     90.00  
Customer B  Blue Avenue      Widget    42.00     78.00     87.00  
Customer B  Blue Avenue      Thing     41.00     68.00     85.00  
Customer B  Green Drive      Widget    38.00     65.00     91.00  
Customer $  Square Crescent  Widget    39.00     62.00     90.00  
Customer $  Circle Way       Widget    34.00     60.00     96.00
I then have my Word document set up to mail merge based on this range, using the following merge code:


So effectively I check to see if I am merging the first item in the merge sequence, outputting the start of a letter if I am, which contains the Dear [x] line and the table header row.

Then I use some logic to determine whether to output the next record from the data source range as a row in the table or to end the letter and start the next one. In this way I should end up with one document per Customer, each with a list of their records from the Excel range, like this:


However, what I actually end up with is something like this:


So is there any way to get this to actually display in a table properly? I have tried moving the closing/terminating double quote at the end of each table row into the final cell on that row, but this breaks the merge entirely, so I cannot see how I can get this to work.

Any suggestions would be most welcome!
Reply With Quote
  #2  
Old 02-07-2012, 03:27 PM
macropod's Avatar
macropod macropod is offline Directory Mail Merge with table layout Windows 7 64bit Directory Mail Merge with table layout Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi 3N1GM4,

Your mergefield code appears to come from my my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...ngs-(2000-2010)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
The tutorial includes a 'TableJoiner' that should be run after the mailmerge to join the tables. The alternative is to format the the mailmerge field coding to insert a paragraph break that is only 1pt high, with no before/after space, as per the 'Calculating Table Totals' example.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-08-2012, 04:12 AM
3N1GM4 3N1GM4 is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Novice
Directory Mail Merge with table layout
 
Join Date: Feb 2012
Posts: 6
3N1GM4 is on a distinguished road
Default

Hi macropod,

Thanks very much for the advice, I have been able to run the TableJoiner macro on my merged document, but unfortunately it only seems to remove the gap between the header row of the table and the first row of data - all subsequent rows are still spaced:

MacroOutput.PNG

What am I missing to get this to join all of the rows correctly?

Thanks.
Reply With Quote
  #4  
Old 02-08-2012, 04:21 AM
macropod's Avatar
macropod macropod is offline Directory Mail Merge with table layout Windows 7 64bit Directory Mail Merge with table layout Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi 3N1GM4,

That suggests that, before running the macro, there's more than one paragraph break between the tables that are supposed to get joined. That, in turn, suggests you have an extra paragraph break (or a space before a required paragraph break) in/after the mergefield in your mailmerge main document. Fix that and it should work OK.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-08-2012, 08:40 AM
3N1GM4 3N1GM4 is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Novice
Directory Mail Merge with table layout
 
Join Date: Feb 2012
Posts: 6
3N1GM4 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Hi 3N1GM4,

That suggests that, before running the macro, there's more than one paragraph break between the tables that are supposed to get joined. That, in turn, suggests you have an extra paragraph break (or a space before a required paragraph break) in/after the mergefield in your mailmerge main document. Fix that and it should work OK.
Thanks for the advice macropod, but I can't seem to find the offending paragraph break in my mailmerge main document.

In fact, I cannot find any paragraph breaks in the document around my tables which I can remove without it causing the layout to break completely (it starts to move double quote characters into the actual cells of my tables if I do, which stops the merge from working at all).

Is there any particular method or process I can follow to troubleshoot this? My screenshot in my OP shows a representation of how my mailmerge main document is laid out.
Reply With Quote
  #6  
Old 02-08-2012, 11:53 PM
macropod's Avatar
macropod macropod is offline Directory Mail Merge with table layout Windows 7 64bit Directory Mail Merge with table layout Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

If you can post a copy of your mailmerge main document and some representative data (nothing sensitive), I'll take a look at it.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 02-09-2012, 02:26 AM
3N1GM4 3N1GM4 is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Novice
Directory Mail Merge with table layout
 
Join Date: Feb 2012
Posts: 6
3N1GM4 is on a distinguished road
Default

Attached is an example data sheet and mail merge document - thanks for having a look at this.

I can see that what you are saying is correct, because prior to running the TableJoiner macro on the merged document, there is only a single linebreak between the header row of each table and the first data row, but there are two between each subsequent pair of data rows - I just can't see how to eradicate this from the mailmerge template document.

Thanks again for your help!
Attached Files
File Type: docx MergeDocument.docx (21.2 KB, 242 views)
File Type: xlsx MergeData.xlsx (8.9 KB, 143 views)
Reply With Quote
  #8  
Old 02-09-2012, 02:45 AM
macropod's Avatar
macropod macropod is offline Directory Mail Merge with table layout Windows 7 64bit Directory Mail Merge with table layout Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi 3N1GM4,

Your document's field construction differs significantly from that shown in the tutorial. For example, if you set the field code display 'on' and select from the first field brace to the first 'IF', in:
• your document, only the first few lines will be selected.
• the tutorial document, all of the fields in the document will be selected.
I suggest trying again, by copying the relevant field from the tutorial and editing the contents to suit your needs.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 02-09-2012, 02:52 AM
3N1GM4 3N1GM4 is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Novice
Directory Mail Merge with table layout
 
Join Date: Feb 2012
Posts: 6
3N1GM4 is on a distinguished road
Default

Ok, I will give this another try, thanks.
Reply With Quote
  #10  
Old 02-09-2012, 04:44 AM
3N1GM4 3N1GM4 is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Novice
Directory Mail Merge with table layout
 
Join Date: Feb 2012
Posts: 6
3N1GM4 is on a distinguished road
Default

Fantastic, all working now and I feel like I've learned a little more thoroughly now exactly how MailMerge works! Just have to fiddle with the exact layout and fields now to get the letters looking exactly how we want them.

Thanks so much for your help macropod, appreciate it.
Reply With Quote
  #11  
Old 06-03-2014, 09:03 PM
natasha_arriell natasha_arriell is offline Directory Mail Merge with table layout Windows 7 64bit Directory Mail Merge with table layout Office 2013
Novice
 
Join Date: Jun 2014
Posts: 4
natasha_arriell is on a distinguished road
Default

Quote:
Originally Posted by 3N1GM4 View Post
Fantastic, all working now and I feel like I've learned a little more thoroughly now exactly how MailMerge works! Just have to fiddle with the exact layout and fields now to get the letters looking exactly how we want them.

Thanks so much for your help macropod, appreciate it.
Hi,

I know you posted this a few years ago. Im trying to do the same. How did you able to joined the tables?
Reply With Quote
  #12  
Old 06-03-2014, 09:54 PM
macropod's Avatar
macropod macropod is offline Directory Mail Merge with table layout Windows 7 32bit Directory Mail Merge with table layout Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The macro to do that is in the tutorial referred to in the links in post #2
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Directory Mail Merge with table layout Mail Merge to create specific number of table rows flackend Mail Merge 4 12-01-2023 02:49 PM
Text preceeding a directory merge entercomp Mail Merge 0 02-13-2010 08:28 PM
Insert formatted table into word (mail merge) manojbmsce Mail Merge 0 09-25-2008 02:25 AM
word catalog table layout problem... turk1130 Word Tables 0 08-01-2006 11:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:52 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft