Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2014, 10:47 AM
stacey_e stacey_e is offline Mail Merge Repeat Record a certain number of times Windows 7 64bit Mail Merge Repeat Record a certain number of times Office 2010 64bit
Novice
Mail Merge Repeat Record a certain number of times
 
Join Date: Apr 2014
Posts: 1
stacey_e is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 04-25-2014, 05:59 PM
macropod's Avatar
macropod macropod is offline Mail Merge Repeat Record a certain number of times Windows 7 32bit Mail Merge Repeat Record a certain number of times 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

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
For a macro to convert the first of the above field text strings to working field codes, see: http://www.gmayor.com/export_field.htm#TextToField
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-17-2018, 01:09 PM
dbrawner dbrawner is offline Mail Merge Repeat Record a certain number of times Windows 10 Mail Merge Repeat Record a certain number of times Office 2019
Novice
 
Join Date: Nov 2018
Posts: 3
dbrawner is on a distinguished road
Default 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?
Reply With Quote
  #4  
Old 11-17-2018, 10:08 PM
macropod's Avatar
macropod macropod is offline Mail Merge Repeat Record a certain number of times Windows 7 64bit Mail Merge Repeat Record a certain number of times 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

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]
Reply With Quote
  #5  
Old 11-18-2018, 04:17 PM
dbrawner dbrawner is offline Mail Merge Repeat Record a certain number of times Windows 10 Mail Merge Repeat Record a certain number of times Office 2019
Novice
 
Join Date: Nov 2018
Posts: 3
dbrawner is on a distinguished road
Default 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
Reply With Quote
  #6  
Old 11-19-2018, 02:07 AM
macropod's Avatar
macropod macropod is offline Mail Merge Repeat Record a certain number of times Windows 7 64bit Mail Merge Repeat Record a certain number of times 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

Quote:
Originally Posted by dbrawner View Post
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...
There is no second page to format per my instructions. All you need do is complete the setup correctly on the first page. As advised in my original post:
Quote:
Originally Posted by macropod View Post
the field codes as depicted above need to be copied to each cell.
Did you do that?
Quote:
Originally Posted by dbrawner View Post
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.
If the field code has been correctly reconstructed, it should do so. Try the code under Convert Text Representations of Fields to Working Fields in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html
Quote:
Originally Posted by dbrawner View Post
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?
After {SET QTY {MERGEFIELD Quantity}}, you could insert a field coded as {FILLIN "Quantity: {REF QTY}"}
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 11-19-2018, 09:38 PM
dbrawner dbrawner is offline Mail Merge Repeat Record a certain number of times Windows 10 Mail Merge Repeat Record a certain number of times Office 2019
Novice
 
Join Date: Nov 2018
Posts: 3
dbrawner is on a distinguished road
Default [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.
Reply With Quote
  #8  
Old 07-07-2023, 01:27 AM
Taka Taka is offline Mail Merge Repeat Record a certain number of times Windows 10 Mail Merge Repeat Record a certain number of times Office 2007
Novice
 
Join Date: Jul 2023
Posts: 1
Taka is on a distinguished road
Default 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:
IF{MERGEREC}= 1 {SET Idx 0}}{IF{Idx}<> {MERGEREC} "{SET Data "{MERGEFIELD Nombre} {MERGEFIELD Articulo}"}{SET QTY {MERGEFIELD Lineas}}{SET Idx {MERGEREC}}"}{SET QTY {=QTY-1}}{REF DATA}{NEXTIF {QTY}= 0
tried before, after the field label, instead of the label, and I don't see how it is, nor have I found more information elsewhere.


Thanks for your help
Attached Files
File Type: xlsx Datos.xlsx (8.7 KB, 2 views)
File Type: docx Sample.docx (14.4 KB, 3 views)

Last edited by Taka; 07-07-2023 at 01:29 AM. Reason: Dont write Thanks
Reply With Quote
Reply

Tags
mail merge

Thread Tools
Display Modes


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 Repeat Record a certain number of times mail merge skips 16th record only - of 23 total. passenger Mail Merge 1 07-24-2012 04:03 PM
Mail Merge Repeat Record a certain number of times 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:12 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