Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Miro Miro is offline
external usenet poster
 
Posts: 4
Default Mail merge - how to print different numbers of copies

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).
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Mail merge - how to print different numbers of copies

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).


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Miro Miro is offline
external usenet poster
 
Posts: 4
Default Mail merge - how to print different numbers of copies

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).



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
I set the numer of copies to 1, but 2 copies print, always. logesbalconies Microsoft Word Help 0 October 16th 07 01:25 PM
how do I print 3 copies of page 1 in a 3 page mail merge? jtrimb Mailmerge 2 November 29th 06 05:32 AM
Print Problems w/ Word 2003 Pro: unable to print multiple copies & envelopes [email protected] Microsoft Word Help 0 March 20th 06 02:34 AM
print copies with consecutive numbers shorty7 Page Layout 4 May 8th 05 03:58 PM
Printing multiple copies of each label using mail merge picklet222 Mailmerge 3 February 2nd 05 04:51 PM


All times are GMT +1. The time now is 02:59 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"