Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    How can I use Word's mailmerge facility to create lists separated by category and what's the coding for this purpose?
    Herewith the sample of excel sheet. In this data I want to do that wherever in city column, when new city appears; this new city starts on next page with table format.

    Thanks in advance,

    NG
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Our moderator and Word MVP macropod has written an extensive tutorial about this type of mail merge: Word 97-2007 Mailmerge Tutorial: Create Sorted Listings.
    The tutorial comes with a sample document and workbook.

  3. #3
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HANS,

    Thanks for giving the tutorial link. But still I have problem to create coding in word document properly. I can not understand how to use the field codes "Primary Key" etc.
    Any suggestions in this regard would be greatly appreciated.

    Warm Regards,

    NG

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'll ask macropod to look at your question.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='ng6971' post='796157' date='03-Oct-2009 17:12']Hi HANS,

    Thanks for giving the tutorial link. But still I have problem to create coding in word document properly. I can not understand how to use the field codes "Primary Key" etc.
    Any suggestions in this regard would be greatly appreciated.

    Warm Regards,

    NG[/quote]
    Hi NG,

    For what you've described, you could use a simple Directory merge with the mergefields in a table and, after the merge is finished, manually split the table where required. Or you could use a macro to automate the splitting.

    Alternatively, you can use the example under the heading 'A Simple Merge to Table By Category', changing all occurrences of 'State' to 'City', 'Representative' to 'Company' and 'Sales' to 'Name'. After completing the merge, you'd run the ‘TableJoiner’ macro.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Macropod,

    Thanks for your quick response. I have upload 2 separate .zip files for your reference to better understand my view.

    Warm Regards

    NG
    Attached Files Attached Files

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='ng6971' post='796216' date='04-Oct-2009 05:34']Hi Macropod,

    Thanks for your quick response. I have upload 2 separate .zip files for your reference to better understand my view.

    Warm Regards

    NG[/quote]
    Hi NG,

    Try the attached. Note that, aside from the side-on landscape arrangement (which should nevertheless print correctly), the apparent column order is reversed. What the merge uses is actually a multi-column document with a single-celled table for each record, overlaying a textbox with another table embedded in it to provide the gridlines you apparently want.

    If that doesn't suit, I think the simplest approach would be to insert empty rows into the data, so that each block has an exact multiple of 24 rows. Do note, though, that whatever approach you take, you're likely to end up with a lot of wasted labels that way. A more economical approach would be to work in multiples of 3 labels so that each group starts on a new row, rather than a new page. That way, you'll waste no more than 2 labels per group, compared with up to 23 if you work at the page level.

    Neither a Directory/Catalog merge or the application of field coding to a label merge (which you can't use to force page separations) will achieve exactly the same result as you might get with a label merge and the grouping of the Excel data into exact multiples of 24 rows. The only other option would be to run a macro against the standard label merge's output to reorganise the tables, but that would require a significant coding effort.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Lounger
    Join Date
    Oct 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Macropod,

    Absolute amazing solution, thank you very much! I really appreciate your work. Works perfect but Two things I want that if page orientation is in potrait and there is page number also in the footer area (Center allignment on page) it will be great.

    Once again thanks & warm regards,

    NG




    [quote name='macropod' post='796254' date='04-Oct-2009 18:30']Hi NG,

    Try the attached. Note that, aside from the side-on landscape arrangement (which should nevertheless print correctly), the apparent column order is reversed. What the merge uses is actually a multi-column document with a single-celled table for each record, overlaying a textbox with another table embedded in it to provide the gridlines you apparently want.

    If that doesn't suit, I think the simplest approach would be to insert empty rows into the data, so that each block has an exact multiple of 24 rows. Do note, though, that whatever approach you take, you're likely to end up with a lot of wasted labels that way. A more economical approach would be to work in multiples of 3 labels so that each group starts on a new row, rather than a new page. That way, you'll waste no more than 2 labels per group, compared with up to 23 if you work at the page level.

    Neither a Directory/Catalog merge or the application of field coding to a label merge (which you can't use to force page separations) will achieve exactly the same result as you might get with a label merge and the grouping of the Excel data into exact multiples of 24 rows. The only other option would be to run a macro against the standard label merge's output to reorganise the tables, but that would require a significant coding effort.[/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •