Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-16-2014, 09:12 AM
screech screech is offline Directory MailMerge with Category Grouped Lists on Labels Windows 7 64bit Directory MailMerge with Category Grouped Lists on Labels Office 2010 32bit
Novice
Directory MailMerge with Category Grouped Lists on Labels
 
Join Date: Jun 2014
Posts: 3
screech is on a distinguished road
Default Directory MailMerge with Category Grouped Lists on Labels

Hello all,

First time here. I've searched around this forum for my answer and I have found a nice tutorial by Paul Edstein that describes how to create category-based lists. I have had success recreating the work in the tutorial and also applying it to my own list however I seem to be stuck with how to apply the categories to a document designed for labels.



I have an Excel table with a list of samples. The table includes a Box #, Project #, Sample #s, and a Date. I am hoping to use MailMerge as a tool to select which labels I would like to print from the directory, and then merge them on the Word Label Template and then print.

I have created a couple of test files for this and I am attaching them to this message. The XLS file is an example table of the data. I am also attaching the word document I using to Finish and Merge to a new document and create the labels.

The problem is that I cannot get the second sample information on the same box label. It always pushes it to the next label and it does not combine them. What I want it to look like is in the second Word attachment, called "Desired Result".

Is there a way to manipulate the form fields with this label template to group the samples by box on a single label as in the desired result? I feel that I have tried a combination of fields but still can't get the desired result. Thank you!
Attached Files
File Type: xlsx Sample List.xlsx (9.0 KB, 11 views)
File Type: doc Sample Labels.doc (40.5 KB, 8 views)
File Type: docx Sample Labels - Desired Result.docx (24.5 KB, 12 views)
Reply With Quote
  #2  
Old 06-16-2014, 05:37 PM
macropod's Avatar
macropod macropod is offline Directory MailMerge with Category Grouped Lists on Labels Windows 7 32bit Directory MailMerge with Category Grouped Lists on Labels Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
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 directory/catalogue merge tools won't combine multiple records into a single table cell. If you want that, you'll need to re-organise the source data, so that each record (i.e. 'box') has all its data on the same row, with different project, sample & date headers for each item. Either that, or you'll need to use a macro-driven process in Excel to automate & populate the Word document, without recourse to mailmerge.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-18-2014, 05:26 AM
screech screech is offline Directory MailMerge with Category Grouped Lists on Labels Windows 7 64bit Directory MailMerge with Category Grouped Lists on Labels Office 2010 32bit
Novice
Directory MailMerge with Category Grouped Lists on Labels
 
Join Date: Jun 2014
Posts: 3
screech is on a distinguished road
Default

Thank you very much for your answer, Paul. I'm disappointed that I cannot do this through MailMerge but thank you for offering an alternative with a macro that could run in the Excel file to push the information to a label template in Word. I'm very familiar with creating and running macros in Excel but I don't have a lot of experience with cross-program code. I will look around the internet later for some examples, but do you have any examples of code or good resources in mind that will get me started down the right road?
Reply With Quote
  #4  
Old 06-18-2014, 11:41 PM
macropod's Avatar
macropod macropod is offline Directory MailMerge with Category Grouped Lists on Labels Windows 7 32bit Directory MailMerge with Category Grouped Lists on Labels Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
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

FWIW, an alternative approach would be to forget about using Word and doing the lot from within Excel. For example, the following macro creates all your labels on Sheet 2 of your existing workbook:
Code:
Sub Demo()
Dim lRow As Long, lCol As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim WkShtSrc As Worksheet, WkShtTgt As Worksheet
k = 0
Set WkShtSrc = ThisWorkbook.Worksheets("Sheet1")
Set WkShtTgt = ThisWorkbook.Worksheets("Sheet2")
With WkShtTgt
  .UsedRange.ClearContents
  .Columns("B:D").ColumnWidth = 43.3
  .Columns("A").ColumnWidth = 10
  .Columns("C").ColumnWidth = 10
  .Rows.RowHeight = 240
  With .Columns("A:D").Font
    .Name = "Arial"
    .Size = 12
  End With
End With
With WkShtSrc
  lRow = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
  lCol = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Column
  For i = 2 To lRow
    If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
      With WkShtTgt.Cells(m, l)
        .Value = .Text & Chr(10)
        For j = 2 To lCol
          .Value = .Text & Chr(10) & WkShtSrc.Cells(i, j)
        Next
      End With
    Else
      l = (k Mod 2 + 1) * 2: k = k + 1: m = -Int(-k / 2)
      With WkShtTgt.Cells(m, l)
        .Value = "BOX " & WkShtSrc.Cells(i, 1) & Chr(10)
        For j = 2 To lCol
          .Value = .Text & Chr(10) & WkShtSrc.Cells(i, j)
        Next
      End With
    End If
  Next
End With
End Sub
You will probably need to adjust the sheet margins and, perhaps, the values used for the columns A & C widths to get the exact alignment you require but, once that's done, there's no need to involve Word.
__________________
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 MailMerge with Category Grouped Lists on Labels SQL MailMerge Labels Mark J Rees Mail Merge 2 05-10-2013 06:00 PM
Directory MailMerge with Category Grouped Lists on Labels Pictorial directory using mailmerge JohnFinNC Mail Merge 6 10-04-2012 06:36 PM
Directory MailMerge with Category Grouped Lists on Labels Directory Mailmerge rohitjain80 Mail Merge 1 02-12-2012 03:42 AM
Excel Multi Level Category labels Spay Excel 0 03-14-2011 01:05 PM
MailMerge Labels Question trims30 Mail Merge 0 08-04-2010 05:21 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:23 PM.


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