[Miro]

I have Excel sheet as source. I'd like to specify in one column how many
times one record should be merged. For example:
Miro 4 - I'd like to print 4 letters to miro (1 of 4, 2 of 4, 3 of 4, 4
of 4)
Linda 5 - Print 5 letters to Linda

and if possible number them 1 of 5, 2 of 5 ... 5 of 5 .

Is there a way to do it with Mail Merge or Excel without duplicating Miro
record 4 times (I have 450 records with numbers from 1 to around 25).

[Peter Jamieson]

Here are a couple of possible approaches. However, I cannot remember if they
take account of the need to sequence number each letter (or label).

See the following conversation in this group:

http://groups.google.co.uk/group/mic...785b1d8e132121

Alternatively, you can
a. use Excel VBA to create a new worksheet with the correct number of
copies of each row. I don' thave code for that right now
b. use Word VBA and the following approach to create the data source you
need

There is another method that may work for you, as long as you know the
maximum quantity. It needs some Word VBA to set up the data source. It may
not work if there are a large number of labels, or if you have "memo" type
data (long text fields).

Suppose your data is in an Excel file called c:\mydata\labels.xls, is in
Sheet1, and has three columns:


k,labeldata, quantity


k needs to be a unique identifier for the record (because it is used in a
sort). If necessary, you might be able to use labeldata or a unique
combination of the other fields for this value


Create a new Excel file called c:\mydata\multiplier.xls. In Sheet1, create
one column called multiplier and put the following data in it:


1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
....
and so on (i.e. you need 15 rows with the number 15, 100 rows with the
number 100. The largest number must be equal to or greater than the maximum
number in your "quantity" column.


Use the following Word VBA to connect to the data source:


Sub ConnectMultiple()
' You can define more of the constants here if you really want
Dim strData As String
Dim strKeyColumn As String
Dim strQuantityColumn As String
Dim strMultiplier As String
strData = "c:\mydata\labels.xls"
strKeyColumn = "k"
strQuantityColumn = "quantity"
strMultiplier= c:\mydata\multiplier.xls"
ActiveDocument.MailMerge.OpenDataSource _
Name:=strData, _
SQLStatement:="SELECT [d].* FROM [" & strData &"].[Sheet1$] [d]" & _
" LEFT JOIN [" & strMultiplier & "].[Sheet1$]
[m]" & _
" ON [d].[" & strQuantityColumn & "] =
[m].[multiplier]" & _
" ORDER BY [d].[" & strKeyColumn & "]"
End Sub


Make sure there is a space after the " in those last few lines.


For further info. on how to install and run Word VBA Macros, see e.g. Graham
Mayor's article at


http://www.gmayor.com/installing_macro.htm


If you do try this please preview the records to check that the correct
number of each record has been inserted. I would also be interested to know
if it works for you.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Miro" wrote in message
...
I have Excel sheet as source. I'd like to specify in one column how many
times one record should be merged. For example:
Miro 4 - I'd like to print 4 letters to miro (1 of 4, 2 of 4, 3 of 4,
4
of 4)
Linda 5 - Print 5 letters to Linda

and if possible number them 1 of 5, 2 of 5 ... 5 of 5 .

Is there a way to do it with Mail Merge or Excel without duplicating Miro
record 4 times (I have 450 records with numbers from 1 to around 25).

[Miro]

Thanks, I got it working in a similar way. I created Macro in Excel to
duplicate records and create all required numbers. Then I just did mail
merge.

Thanks again for taking time to answer.

"Peter Jamieson" wrote:

Here are a couple of possible approaches. However, I cannot remember if they
take account of the need to sequence number each letter (or label).

See the following conversation in this group:

http://groups.google.co.uk/group/mic...785b1d8e132121

Alternatively, you can
a. use Excel VBA to create a new worksheet with the correct number of
copies of each row. I don' thave code for that right now
b. use Word VBA and the following approach to create the data source you
need

There is another method that may work for you, as long as you know the
maximum quantity. It needs some Word VBA to set up the data source. It may
not work if there are a large number of labels, or if you have "memo" type
data (long text fields).

Suppose your data is in an Excel file called c:\mydata\labels.xls, is in
Sheet1, and has three columns:


k,labeldata, quantity


k needs to be a unique identifier for the record (because it is used in a
sort). If necessary, you might be able to use labeldata or a unique
combination of the other fields for this value


Create a new Excel file called c:\mydata\multiplier.xls. In Sheet1, create
one column called multiplier and put the following data in it:


1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
....
and so on (i.e. you need 15 rows with the number 15, 100 rows with the
number 100. The largest number must be equal to or greater than the maximum
number in your "quantity" column.


Use the following Word VBA to connect to the data source:


Sub ConnectMultiple()
' You can define more of the constants here if you really want
Dim strData As String
Dim strKeyColumn As String
Dim strQuantityColumn As String
Dim strMultiplier As String
strData = "c:\mydata\labels.xls"
strKeyColumn = "k"
strQuantityColumn = "quantity"
strMultiplier= c:\mydata\multiplier.xls"
ActiveDocument.MailMerge.OpenDataSource _
Name:=strData, _
SQLStatement:="SELECT [d].* FROM [" & strData &"].[Sheet1$] [d]" & _
" LEFT JOIN [" & strMultiplier & "].[Sheet1$]
[m]" & _
" ON [d].[" & strQuantityColumn & "] =
[m].[multiplier]" & _
" ORDER BY [d].[" & strKeyColumn & "]"
End Sub


Make sure there is a space after the " in those last few lines.


For further info. on how to install and run Word VBA Macros, see e.g. Graham
Mayor's article at


http://www.gmayor.com/installing_macro.htm


If you do try this please preview the records to check that the correct
number of each record has been inserted. I would also be interested to know
if it works for you.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Miro" wrote in message
...
I have Excel sheet as source. I'd like to specify in one column how many
times one record should be merged. For example:
Miro 4 - I'd like to print 4 letters to miro (1 of 4, 2 of 4, 3 of 4,
4
of 4)
Linda 5 - Print 5 letters to Linda

and if possible number them 1 of 5, 2 of 5 ... 5 of 5 .

Is there a way to do it with Mail Merge or Excel without duplicating Miro
record 4 times (I have 450 records with numbers from 1 to around 25).