Solved

Recursion / Iteration in Word 2013 Mail Merge

Posted on 2016-09-26
16
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 21
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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 21
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

My experience with Windows 10 over a one year period and suggestions for smooth operation
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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