#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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?
|
#4
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL MailMerge Labels | Mark J Rees | Mail Merge | 2 | 05-10-2013 06:00 PM |
Pictorial directory using mailmerge | JohnFinNC | Mail Merge | 6 | 10-04-2012 06:36 PM |
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 |