Solved

Recursion / Iteration in Word 2013 Mail Merge

Posted on 2016-09-26
16
50 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 17

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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now