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 / June 2007

Tip: Looking for answers? Try searching our database.

mail merge

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Denys - 23 Jun 2007 18:52 GMT
Thanks for helping me as I am a beginner with mail merge.
I have an Access database with information regarding perennial plants.  I
need to print labels for each one of the plants. My problem is that I can't
figure out the mail merge to print more than one label at times.  let's say
I have plant "A" with a quantity of 20, I want to mail merge to print 20
labels of Plant "A" as well as carrying on printing the other labels also.
Is it possible?

Thanks
Denys
Doug Robbins - Word MVP - 23 Jun 2007 21:51 GMT
To do this, first you will need to create a catalog (or in Word XP and
later, it is called "directory") type mailmerge in the maindocument of which
you insert a one row table into the cells of which you insert the field
names from your data source.
If the number of labels required for each record is in the data source,
insert the name of that field in the last cell of the row.  If it is not
contained in the data source, have one empty cell at the end of the row.

Then you execute that merge to a new document.  The new document that is
created will contain a row of data for each record in the data source.  If
the data source did not include a field with the required number of each
label, enter those numbers in the last column of the table in this new
document.

Then with that document as the active document, run a macro that contains
the following code:

Dim i As Long, j As Long, k As Long, numlabels As Long
Dim source As Document
Dim stable As Table, ttable As Table
Dim arow As Row
Dim numrange As Range

Set source = ActiveDocument
Set stable = source.Tables(1)
k = stable.Columns.Count
For i = stable.Rows.Count To 1 Step -1
   Set numrange = stable.Cell(i, k).Range
   numrange.End = numrange.End - 1
   numlabels = Val(numrange.Text)
   If numlabels > 1 Then
       stable.Rows(i).Range.Copy
       For j = 2 To numlabels
           stable.Rows(i).Range.Paste
       Next j
   End If
Next i

This will increase the number of rows of data for each record to the number
of labels that you want.  Now, you must insert a row at the top of this
document and enter the field names into the cells of that row.  Then save
this document and use it as the data source for creating your labels by mail
merge.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thanks for helping me as I am a beginner with mail merge.
> I have an Access database with information regarding perennial plants.  I
[quoted text clipped - 6 lines]
> Thanks
> Denys
Denys - 23 Jun 2007 23:30 GMT
Hi Doug,
I did what you said but it is not working.  Maybe I need to change something
in the macro language??  The field containing the number of each label is
called: "Quantity".  Not sure where to go from here.

Tx
Denys

> To do this, first you will need to create a catalog (or in Word XP and
> later, it is called "directory") type mailmerge in the maindocument of which
[quoted text clipped - 50 lines]
> > Thanks
> > Denys
Doug Robbins - Word MVP - 23 Jun 2007 23:41 GMT
Tell me exactly what you did.

Did you have the quantity field as the last field in the catalog or
directory type mail merge main document?

When you executed that merge to a new document, what did you get?

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hi Doug,
> I did what you said but it is not working.  Maybe I need to change
[quoted text clipped - 65 lines]
>> > Thanks
>> > Denys
Denys - 24 Jun 2007 00:54 GMT
ok, I'm using Office XP.  the page has 10 row/labels.
each label contains:

«Next Record» «Name» «Cultivar»
«CommonName»
Potted on:«DateRepotted» ID#:«IDNumber» «Quantity»

except for the first row where the <next record> is not there.

When I executed the merge to a new document, I get several pages of labels
but only one label of each, some records have 2 - 12 as quantity, but only
one label is printed.

I executed the macro after the new document was created, and nothing happened.

Denys

> Tell me exactly what you did.
>
[quoted text clipped - 72 lines]
> >> > Thanks
> >> > Denys
Doug Robbins - Word MVP - 24 Jun 2007 04:40 GMT
No wonder it does not work. You have not followed the instructions that I
gave you which were:

To do this, first you will need to create a catalog (or in Word XP and
later, it is called "directory") type mailmerge in the maindocument of which
you insert a one row table into the cells of which you insert the field
names from your data source.
If the number of labels required for each record is in the data source,
insert the name of that field in the last cell of the row.  If it is not
contained in the data source, have one empty cell at the end of the row.

Then you execute that merge to a new document.  The new document that is
created will contain a row of data for each record in the data source.  If
the data source did not include a field with the required number of each
label, enter those numbers in the last column of the table in this new
document.

Then with that document as the active document, run a macro that contains
the following code:

Dim i As Long, j As Long, k As Long, numlabels As Long
Dim source As Document
Dim stable As Table, ttable As Table
Dim arow As Row
Dim numrange As Range

Set source = ActiveDocument
Set stable = source.Tables(1)
k = stable.Columns.Count
For i = stable.Rows.Count To 1 Step -1
   Set numrange = stable.Cell(i, k).Range
   numrange.End = numrange.End - 1
   numlabels = Val(numrange.Text)
   If numlabels > 1 Then
       stable.Rows(i).Range.Copy
       For j = 2 To numlabels
           stable.Rows(i).Range.Paste
       Next j
   End If
Next i

This will increase the number of rows of data for each record to the number
of labels that you want.  Now, you must insert a row at the top of this
document and enter the field names into the cells of that row.  Then save
this document and use it as the data source for creating your labels by mail
merge.

Please read the whole thing and do exactly as I have instructed WITHOUT
LEAVING OUT ANY OF THE STEPS.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> ok, I'm using Office XP.  the page has 10 row/labels.
> each label contains:
[quoted text clipped - 106 lines]
>> >> > Thanks
>> >> > Denys
Denys - 24 Jun 2007 19:02 GMT
I'm sorry about your frustration, I finally got it work. Being a newbie at
this, I had a hard time understanding the process.

THanks so much for your help and patience.

Denys

> No wonder it does not work. You have not followed the instructions that I
> gave you which were:
[quoted text clipped - 156 lines]
> >> >> > Thanks
> >> >> > Denys
hirendra7158 - 25 Jun 2007 15:26 GMT
denys,
I did not exactly follow the instructins laid by Mr. Robbins since this code
things are still out of my reach. Will you please be kind enough to describe
me step by step again, and how did you do it, for me?
I seem to be a more recent newbie than you!.
thabnking you.
Dr. H.R.Shah

> I'm sorry about your frustration, I finally got it work. Being a newbie at
> this, I had a hard time understanding the process.
[quoted text clipped - 163 lines]
> > >> >> > Thanks
> > >> >> > Denys
Doug Robbins - Word MVP - 26 Jun 2007 09:43 GMT
See the article "What do I do with macros sent to me by other newsgroup
readers to help me out?" at:

http://www.word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> denys,
> I did not exactly follow the instructins laid by Mr. Robbins since this
[quoted text clipped - 203 lines]
>> > >> >> > Thanks
>> > >> >> > Denys
Curt - 24 Jun 2007 14:34 GMT
Will be able to use this in my next endevor
Thanks Doug

> To do this, first you will need to create a catalog (or in Word XP and
> later, it is called "directory") type mailmerge in the maindocument of which
[quoted text clipped - 50 lines]
> > Thanks
> > Denys
 
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.