[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Recursion / Iteration in Word 2013 Mail Merge

I have created a mail merge to make labels in Word 2013 from a CSV data source.  I am new to mail merge but picked up the basics well enough - I can create my labels in the format I want, but there is a wrinkle to the process.  Each record in the CSV contains a field with a number "n", and in the merge I need "n" identical copies of the same label.  For example if that field held the number three, I need to generate 3 identical labels from that record before progressing on to the next record.

I have identified the functionality to define variables and use If Then statements but there does not seem to be any options for iteration or recursion.  If the functionality exists please let me know, if not does anyone have any other suggestions?
0
Greg Atkinson
Asked:
Greg Atkinson
  • 8
  • 5
  • 2
  • +1
2 Solutions
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Do you happen to have Access installed? If so, the information in the data document could be in Access and a Numbers table (that simply has numbers ... 1,2,3,...) can be used to create a query to get the number of copies that you want.  Word could then use this query.
0
 
Greg AtkinsonAuthor Commented:
Thanks for the suggestion but unfortunately no I dont have access.
0
 
xtermieCommented:
I don't think that there is functionality to do that, straight forward at least.
However, you could try to prepare the CSV file to include the extra labels, prior to merging the document.
For example you can open the CSV in Excel and create a macro that would
1) read n field and depending on n
2) insert n-1 rows below the record
3) copy the record to those rows

Something like this would work:
Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
'Assume your value is in Column C
For i = 1 To LR
    n = Cells(i, 3).Value
    If n > 1 Then
    Cells(i, 3).EntireRow.Select
    Selection.Copy
    For j = 1 To n - 1
        Cells(i + 1, 3).EntireRow.Insert
    Next
    End If
Next i
End Sub

Open in new window

That way your data file will be properly prepared and you will get the outcome you want
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GrahamSkanRetiredCommented:
You can run the Merge under VBA control with a macro like this:
Sub MergeRepeatingRecords()
    Dim r As Integer
    Dim wdDoc As Document
    Dim n As Integer
    Dim iLastRecord As Integer
    
    Set wdDoc = ActiveDocument
    With wdDoc.MailMerge
        'establish last record number
        .DataSource.ActiveRecord = wdLastDataSourceRecord
        iLastRecord = .DataSource.ActiveRecord
        
        .Destination = wdSendToNewDocument
        'step through Datasource records
        r = 0
        Do Until r = iLastRecord
            .DataSource.LastRecord = r
            .DataSource.FirstRecord = r
            For n = 1 To .DataSource.DataFields("RepeatNumber").Value
                .Execute
            Next n
            r = r + 1
        Loop
    End With
End Sub

Open in new window

0
 
Greg AtkinsonAuthor Commented:
Thankyou for the replies Graham and xtermie.  This stuff is not my area of expertise so I will have to sit down and ponder your solutions for a bit to get my head around them.  Will let you know how I go asap :-)
0
 
Greg AtkinsonAuthor Commented:
Graham I'm not sure if I have understood or executed your instructions correctly, but the result I am getting is multiple documents each with one copy of each label - was that your intent?  What I am trying to acheive is one single document where there is for example 3 copies of label 1 then 2 copies of label 2 etc as  required.

If it helps I have included Labels.docxtickets.csvthe word doc (without your macro as it wont allow me to attach docm files) and a sample of the data source file.  The field No_of_Panels is the one that contains the number that specifies the number of repeats.
0
 
GrahamSkanRetiredCommented:
I am sorry. I didn't  test it with labels, so that result was unexpected. I am trying  to find a way of controlling the unwanted page advancing.
0
 
Greg AtkinsonAuthor Commented:
No need for apologies, your help is appreciated :-)
0
 
Greg AtkinsonAuthor Commented:
Xtermie,

I haven't heard back from Graham re the Word Macro option so I was playing around with your suggestion but when I run the Macro I get run-time error 13, type mismatch.  All I have change from your code is changed 3 in the Cells to 14 as my value is in Column N.  Is there anything else i should have changed?
0
 
Greg AtkinsonAuthor Commented:
If it helps when i click debug it highlights the line -

For j = 1 To n - 1
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
looks like j and n both need to be DIMensioned. Assuming Long is the right data type, add this line to the top by the other ones
DIM j as long, n as long

Open in new window

0
 
GrahamSkanRetiredCommented:
Sorry I got diverted for a while.

I cannot see any way of making my suggestion work. Each time the .Execute method is used it starts a new page, with no information about what was done previously.
0
 
GrahamSkanRetiredCommented:
0
 
Greg AtkinsonAuthor Commented:
I still can't get Xtermie's solution to work.
0
 
GrahamSkanRetiredCommented:
I've tweaked Xtermie's code a bit.

The code now starts at the last row and works backwards, so that the newly inserted rows aren't used for input. Also the insert seems to clear the clipboard, so that a second Insert, was actually inserting a blank line. Together it meant that the Cell being read in line 6
 n = Cells(i, 3).Value

Open in new window

was empty at one point, so not an integer.

Sub test2()
Dim LR As Long
Dim r As Long
Dim c As Integer
Dim n As Integer
Dim j As Integer

LR = Range("A" & Rows.Count).End(xlUp).Row
'Assume your value is in Column J (10)
c = 10

For r = LR To 2 Step -1
    n = Cells(r, c).Value
    For j = 1 To n - 1
        Cells(r, c).EntireRow.Copy
        Cells(r, c).EntireRow.Insert xlShiftDown
    Next
Next r
End Sub

Open in new window

1
 
Greg AtkinsonAuthor Commented:
Thanks Graham, works beautifully.  I have marked yours as the best solution with an assist to xtermie for coming up with the idea.
1

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now