Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Recursion / Iteration in Word 2013 Mail Merge

Posted on 2016-09-26
16
Medium Priority
?
109 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 23
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 1000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 23
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 1000 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

876 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