#1
|
|||
|
|||
Mail Merge Repeat Record a certain number of times
Hi, I have a document that I need to be able to merge certain records a certain number of times before moving to the next record. The number of times would be a number field in my data source. Can this be done within a mail merge?
|
#2
|
||||
|
||||
You can do it via fields coded as:
{IF{MERGEREC}= 1 {SET Idx 0}}{IF{Idx}<> {MERGEREC} "{SET Data "{MERGEFIELD FIRST_NAME} {MERGEFIELD LAST_NAME}"}{SET QTY {MERGEFIELD Quantity}}{SET Idx {MERGEREC}}"}{SET QTY {=QTY-1}}{REF DATA}{NEXTIF {QTY}= 0} or: {IF{MERGEREC}= 1 {SET Idx 0}}{IF{Idx}<> {MERGEREC} "{SET Data "«FIRST_NAME» «LAST_NAME»"}{SET QTY {MERGEFIELD Quantity}}{SET Idx {MERGEREC}}"}{SET QTY {=QTY-1}}{REF DATA}{NEXTIF {QTY}= 0} The bold portion is where you insert your mergefields, together with whatever paragraphs and other formatting you require. The above example assumes data fields named 'FIRST_NAME' and 'LAST_NAME'. Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field construction are all required. Furthermore, the field codes as depicted above need to be copied to each cell. If you use Word's 'update labels' function, that will insert an unwanted «NextRecord» field at the start of all labels after the first. In addition, having constructed the first label page, you need to copy the entire label table and paste onto its end however many copies you need to ensure all of the required labels are catered for. If you don't do this, one set of labels will be skipped at each page break. Once you've done the first page setup, the following macro automates the addition of however many pages of labels might be needed, executes the merge, then restores the document to its original state: Code:
Sub RunMultiLabelMerge() Dim r As Single, c As Single, h As Single, v As Single Dim i As Long, j As Long, x As Long, y As Long, z As Long ' Turn Off Screen Updating Application.ScreenUpdating = False With ActiveDocument 'Temporarily delete the fields (for processing speed) .Fields.Unlink With .MailMerge.DataSource 'Get the label count For i = 1 To .RecordCount .ActiveRecord = i j = j + .DataFields("Quantity").Value Next .ActiveRecord = 1 .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With 'Restore the deleted the fields .Undo 'Calculate the # labels per page With .Tables(1) With .Cell(1, 1) r = .Height: c = .Width End With If .Cell(1, 2).Width = .Cell(1, 1).Width Then h = c x = .Columns.Count Else h = c + .Cell(1, 2).Width x = -Int(-.Columns.Count / 2) End If If .Cell(2, 1).Height = .Cell(1, 1).Height Then v = r y = .Rows.Count Else v = v + .Cell(2, 1).Height y = -Int(-.Rows.Count / 2) End If 'Calculate the # label pages required z = Int(j / (x * y)) y = .Rows.Count 'Add the required # label pages If z > 0 Then .Range.Copy For i = 1 To z ActiveDocument.Paragraphs.Last.Range.Paste Next End With 'Execute the mailmerge .MailMerge.Execute 'Restore the document to its original state If z > 0 Then .Range.Paste: .UndoClear: .Saved = True End With ' Restore Screen Updating Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Code does not move to next record after reaching count
Your code for repeating a merge record n times based on a database field value was just what I was looking for...
The only issues I'm having with the actual running of the code are; 1. it does not move to the next record after the required count is reached. In fact the record jut repeats over and over until the label page is full. 2. the second issue is formatting. I need to insert line feeds or carriage returns between the mergefields. Here is the code I am using; {IF{MERGEREC}= 1 {SET Idx 0}}{IF{Idx}<> {MERGEREC} "{SET Data "{MERGEFIELD LABEL_NAME} {MERGEFIELD SKU}"}{SET QTY {MERGEFIELD Quantity}}{SET Idx {MERGEREC}}"}{SET QTY {=QTY-1}}{REF DATA}{NEXTIF {QTY}= 0} I have a subset of data in a spreadsheet with only two records each having a quantity value of three (3). When I copy the code into 4 rows of 4 columns on label merge (using MS365 Word), only one record is repeated in all 16 labels. Can you help me? |
#4
|
||||
|
||||
Are you actually running the macro designed for this in a mailmerge main document set up for a letter merge (or at least a label merge without the «NextRecord» fields)?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Code does not move to next record after reaching count
Macropod,
Thanks for the reply, Yes, I am running the code on a mailmerge document for labels. I confirmed the <<next record>> was not present in any label space on the document, and since I was only expecting 6 records to be returned, did not format a second page per your instructions... I built the code from your post using a macro to convert the text to code. This successfully returned the price, label name, and sku values from the first record in my dataset but did not progress to the next record when the specified three iterations from the quantity field where reached. Is there a way to step through the mail merge process to confirm the QTY actually set to the correct value and counts down as expected, and a next record is retrieved when QTY = 0? Dave |
#6
|
||||
|
||||
Quote:
Quote:
Quote:
After {SET QTY {MERGEFIELD Quantity}}, you could insert a field coded as {FILLIN "Quantity: {REF QTY}"}
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
[Solved] Code does not move to next record after reaching count
Macroprod,
Thanks so much for your patience... I had used the referenced script to convert text to code... I had copied the fields to each label as per your original instructions. After a careful re-read of your tips and tricks, I double checked the data type in the Quantity field of my dataset. It was in fact a number as text. Converting that column to a number seems to have solved the issue. Your code does in fact work as advertised, I thank you profusely for sharing it. Now on to the matter of formatting the label and including a barcode for the SKU. Not a request, just a musing. Thanks again for your invaluable assistance. |
#8
|
|||
|
|||
sorry I would like to reopen this topic, I can't get it to work
I have my data file with the following records:
Lineas;Nombre;Articulo 2;Jose;Plantas 1;Juan;Verduras 1;manuel; carne and i want to get this result 2;Jose;Plantas 2;Jose;Plantas 1;Juan;Verduras 1;manuel; carne I have used the combination fields, to obtain a normal combination of them, but I cannot get the macro to work, and the corresponding code. I have modified the values FIRST_NAME for Nombre, LAST_NAME for Articulo and Quantity for Lineas, and nothing. I have Quote:
Thanks for your help Last edited by Taka; 07-07-2023 at 01:29 AM. Reason: Dont write Thanks |
Tags |
mail merge |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Repeat formula 5 times and repeat? | Jenny345 | Excel | 4 | 06-14-2013 04:37 PM |
mail merge skips 16th record only - of 23 total. | passenger | Mail Merge | 1 | 07-24-2012 04:03 PM |
Mail merge to print each record as it is created | PNGento | Mail Merge | 1 | 01-26-2012 02:00 PM |
avoid duplicete record and merge the record with the existed record | hemant.behere | Excel | 0 | 01-10-2012 02:53 AM |
Extraneous record in Word Mail Merge | aldo13 | Mail Merge | 0 | 01-23-2006 01:58 PM |