Can I print designated number of label copies based on # in dbase

[joes]

I have an Excel database (2003) with 6 fields for each record. One of the
fields is the number of labels neededto be printed for that record. The
number may differ from record to record. Is there a way to designate how
many labels should be printed for that record in a Word (2003) mailmerge?
Alternatively, is there a way to copy the respective records in Excel the
appropriate number of times with a macro or otherwise so each can then be
used in the mailmerge to print the required number of labels per record? The
Word approach might be more effective if possible since I'm not an
experienced 2003 Excel macro writer.

 

[Doug Robbins - Word MVP]

Use your existing data source with a Directory type mailmerge in the main
document of which you have the merge fields in the cells of a one row table,
with the mergefield that contains the number of labels required in the first
cell in that row. Then execute that merge to a new document and then with
that new document as the active document, run a macro containing the
following code:

Dim Source As Document, Target As Document
Dim stable As Table, ttable As Table
Dim trow As Row
Dim num As Range
Dim i As Long, j As Long, k
Dim data As Range
Set Source = ActiveDocument
Set stable = Source.Tables(1)
i = stable.Columns.Count
Set Target = Documents.Add
Set ttable = Target.Tables.Add(Target.Range, 1, i)
With stable
For i = 1 To .Rows.Count
Set num = .Rows(i).Cells(1).Range
num.End = num.End - 1
For j = 1 To num
Set trow = ttable.Rows.Add
For k = 1 To .Columns.Count
Set data = .Cell(i, k).Range
data.End = data.End - 1
trow.Cells(k).Range.Text = data.Text
Next k
Next j
Next i
End With

It will create a new document containing a table with the required number of
copies of each record so that after you enter the text to be used as the
names of the mergefields in the first row of the table, the document can be
used as the data source for the merge that you want to create.

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

 

[ebuyitnet]

Doug:

Please forgive me but my father posted this question for me and I am the one
who needs the help. The ultimate goal here is to have an excel file when I
am done with this process that has the label/record repeated the number of
times that I need the label printed. Here is an example of my data in excel.

txtQuantityShipped sku description showspecial ROUNDEDPRICE
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL $3.00
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL $4.00

sorry for the formatting but I think you get the idea of what the data looks
like.


Is there a macro that I can run to create a new excel file with the proper
number of labels/records for each item? I have tried the macro you posted in
word and I am getting a run error and when I debug it highlights the Set
stable = Source.Tables(1) and will not complete. I am not knowledgeable at
all with macros and would have no idea what I am doing wrong so if you can
help me in excel, it might be easier since the data is already there.

Thanks again for the help.

Use your existing data source with a Directory type mailmerge in the main
document of which you have the merge fields in the cells of a one row table,
with the mergefield that contains the number of labels required in the first
cell in that row. Then execute that merge to a new document and then with
that new document as the active document, run a macro containing the
following code:

Dim Source As Document, Target As Document
Dim stable As Table, ttable As Table
Dim trow As Row
Dim num As Range
Dim i As Long, j As Long, k
Dim data As Range
Set Source = ActiveDocument
Set stable = Source.Tables(1)
i = stable.Columns.Count
Set Target = Documents.Add
Set ttable = Target.Tables.Add(Target.Range, 1, i)
With stable
For i = 1 To .Rows.Count
Set num = .Rows(i).Cells(1).Range
num.End = num.End - 1
For j = 1 To num
Set trow = ttable.Rows.Add
For k = 1 To .Columns.Count
Set data = .Cell(i, k).Range
data.End = data.End - 1
trow.Cells(k).Range.Text = data.Text
Next k
Next j
Next i
End With

It will create a new document containing a table with the required number of
copies of each record so that after you enter the text to be used as the
names of the mergefields in the first row of the table, the document can be
used as the data source for the merge that you want to create.

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

 

[Doug Robbins - Word MVP]

Have you followed the whole procedure that I suggested?

When I start with the information from your post and put it into Excel and
then follow the procedure, the following is the table that is created:

txtQuantityShipped sku description showspecial ROUNDEDPRICE
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL
$3.00
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL
$4.0
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL
$4.0
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL
$4.0
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL
$4.0
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL
$4.0


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

Doug:

Please forgive me but my father posted this question for me and I am the
one
who needs the help. The ultimate goal here is to have an excel file when
I
am done with this process that has the label/record repeated the number of
times that I need the label printed. Here is an example of my data in
excel.

txtQuantityShipped sku description showspecial ROUNDEDPRICE
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL $3.00
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL
$4.00

sorry for the formatting but I think you get the idea of what the data
looks
like.


Is there a macro that I can run to create a new excel file with the proper
number of labels/records for each item? I have tried the macro you posted
in
word and I am getting a run error and when I debug it highlights the Set
stable = Source.Tables(1) and will not complete. I am not knowledgeable
at
all with macros and would have no idea what I am doing wrong so if you can
help me in excel, it might be easier since the data is already there.

Thanks again for the help.

 

[Peter Jamieson]

1. You could use the following macro to create a new sheet at the
beginning of the same workbook. You should run this on a copy of your
workbook rather than the original.

This macro is based on the following article:

http://groups.google.co.uk/group/microsoft.public.excel.misc/msg/d0a3ef8bd3aea36f?dmode=source

Sub CreateCopiesForLabels()
' the column letter that contains the count
Const CountColumn = "A"
' 1st row is the header, so start with 2
Const FirstRow = 2
Dim lngLastRow As Long
Dim lngRow As Long
Dim varCopies As Variant
Dim wksSource As Excel.Worksheet
Dim wksTarget As Excel.Worksheet

Set wksSource = ActiveSheet
wksSource.Copy Before:=Worksheets(1)
Set wksTarget = ActiveSheet

With wksTarget
' the first data row is row 2
lngFirstRow = 2
lngLastRow = .Cells(.Rows.Count, CountColumn).End(xlUp).Row
For lngRow = lngLastRow To FirstRow Step -1
varCopies = .Cells(lngRow, CountColumn).Value
If IsNumeric(varCopies) Then
If varCopies > 1 Then
.Rows(lngRow + 1).Resize(varCopies - 1).Insert
.Rows(lngRow + 1).Resize(varCopies - 1).Value _
= .Rows(lngRow).Value
End If
End If
Next lngRow
End With

Set wksTarget = Nothing
Set wksSource = Nothing
End Sub


Peter Jamieson

http://tips.pjmsn.me.uk

Doug:

Please forgive me but my father posted this question for me and I am the one
who needs the help. The ultimate goal here is to have an excel file when I
am done with this process that has the label/record repeated the number of
times that I need the label printed. Here is an example of my data in excel.

txtQuantityShipped sku description showspecial ROUNDEDPRICE
10 050CFROD 36 LENGTH .050 DIAMETER CARBON FIBER ROD SHOW SPECIAL $3.00
5 057CFBAR 36 LENGTH .057 X .177 FLAT CARBON FIBER BAR SHOW SPECIAL $4.00

sorry for the formatting but I think you get the idea of what the data looks
like.


Is there a macro that I can run to create a new excel file with the proper
number of labels/records for each item? I have tried the macro you posted in
word and I am getting a run error and when I debug it highlights the Set
stable = Source.Tables(1) and will not complete. I am not knowledgeable at
all with macros and would have no idea what I am doing wrong so if you can
help me in excel, it might be easier since the data is already there.

Thanks again for the help.

 

[ebuyitnet]

Peter:

Just wanted to say thank you. This worked great. I am sorry it took so
long to respond but I got really busy. I do really appreciate your help and
your quick response. This saves me from having to put it into word in the
first place.

 

[Peter Jamieson]

Always useful to know when something works - thanks for the feedback.

Peter Jamieson

http://tips.pjmsn.me.uk