There are several possible approaches to this - probably a bit of VBA
code in Excel or Word to produce a new data source with the correct
number of copies for each record would actually be the best, but another
approach that /may/ work, but does not require VBA is as follows. It may
not work with large data volumes, or if you have over about 64 columns,
some with multiline data, and so on.
1. Let's say your workbook is in a file called c:\mywbs\myworkbook.xlsx
and that your data is in a sheet called "Sheet1"
2. Create a new workbook called c:\mywbs\mycopies.xlsx, put the
following data in column one of "Sheet1" (so the text "copies" is in
cell A1)
copycount
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
6
6
6
6
6
6
This is based on your statement that you need up to 5 copies, and that
you may mean "an original plus up to five copies". If you need more
copies, you can (if you mean "original and up to five copies" then add
6 rows with the number 6 in it.
3. Save and close that workbook.
4. In Sheet1 in "myworkbook.xlsx" you will need
a. a column that contains the total number of copies (i.e.
original+copies) for each letter. Let's say that column is called "copies"
b. preferably, a column that uniquely identifies each letter (let's
call that column "mykey"
First, as a test, in a new Word document, insert the following field -
notice that the backslashes in the pathnames are all doubled up.
{ DATABASE \d "c:\\mywbs\\myworkbook.xlsx"
\c "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\\mywbs\\myworkbook.xlsx;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=37"
\s "SELECT * FROM [Sheet1$]" \h }
Select that field code and press F9 to execute it. If necessary use
Alt-F9 to view the results. If it has worked, you should see all the
rows in your data source in a table (If you have more than around 64
columns in your worksheet, the results should be tab-delimited rows of
data). If you save this document and close it, you should be able to use
it as the data source for a merge.
Now change the SELECT to be as follows:
"SELECT s1.* FROM [Sheet1$] s1 INNER JOIN
[c:\\mywbs\\mycopies.xlsx].[Sheet1$] s2 ON s1.copies = s2.copycount
ORDER BY s1.mykey"
Select and re-execute that field, and you should see the correct number
of copies of each row. Again, you should be able to save and close that
document and use it as a data source.
If you only need some of the columns from the data source, you can list
them in the SELECT, e.g.
"SELECT s1.mykey,s1.copies,s1.field1,s1.field2 FROM [Sheet1$] s1 INNER
JOIN [c:\\mywbs\\mycopies.xlsx].[Sheet1$] s2 ON s1.copies = s2.copycount
ORDER BY s1.mykey"
Worth a try anyway?
Peter Jamieson
http://tips.pjmsn.me.uk