Solved

Recursion / Iteration in Word 2013 Mail Merge

Posted on 2016-09-26
16
88 Views
Last Modified: 2016-10-25
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
Comment
Question by:Greg Atkinson
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 19
ID: 41817311
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
 

Author Comment

by:Greg Atkinson
ID: 41817315
Thanks for the suggestion but unfortunately no I dont have access.
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 250 total points
ID: 41817348
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41818131
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
 

Author Comment

by:Greg Atkinson
ID: 41818990
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
 

Author Comment

by:Greg Atkinson
ID: 41819033
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41820537
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
 

Author Comment

by:Greg Atkinson
ID: 41820962
No need for apologies, your help is appreciated :-)
0
 

Author Comment

by:Greg Atkinson
ID: 41837746
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
 

Author Comment

by:Greg Atkinson
ID: 41837748
If it helps when i click debug it highlights the line -

For j = 1 To n - 1
0
 
LVL 19
ID: 41837785
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41838007
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41858330
0
 

Author Comment

by:Greg Atkinson
ID: 41859446
I still can't get Xtermie's solution to work.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 250 total points
ID: 41859557
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
 

Author Closing Comment

by:Greg Atkinson
ID: 41859610
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Outlook Free & Paid Tools
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question