Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / October 2007

Tip: Looking for answers? Try searching our database.

repeat a print

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Manos - 24 Oct 2007 09:17 GMT
Dear all

I export data from a programe which drop all exports in excel and then
direct to word for print. i have a field which givem e the quantity of an
item.
How can i declare in the mail merge to print a label of this item so many
times as the quantity, form the specific field?

Any examples?

Thanks in advance
Manos
Peter Jamieson - 24 Oct 2007 12:59 GMT
Have a look (e.g. using Google Groups) at the conversation titled "Merge
recipient multiple times" started by "Laser Apparel" on 11 Oct 2007.

Signature

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

> Dear all
>
[quoted text clipped - 8 lines]
> Thanks in advance
> Manos
Peter Jamieson - 24 Oct 2007 14:18 GMT
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.
Signature

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

> Dear all
>
[quoted text clipped - 8 lines]
> Thanks in advance
> Manos
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.