Printing multiple labels based on contents of cell

I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products to
be ordered in columns B-Z. Most cells are empty, but if someone orders a
product, the number of items ordered for each product is in the corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and print
as many labels as there are products ordered, for example, 2 labels for John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

What is actually to be printed on the labels? The name of the product?
What is the data source?

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products to
be ordered in columns B-Z. Most cells are empty, but if someone orders a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and
print
as many labels as there are products ordered, for example, 2 labels for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

I take it this order form is a Word document with a single table? In which
case you can create a merge data source from that form, with which you can
create a label merge
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm . The following
macro will do that for however many columns you have.

Sub LabelData()
Dim oSource As Document
Dim oTarget As Document
Dim oTable As Table, oData As Table
Dim oRow As Row, oNewRow As Row
Dim oCell As Cell
Dim oRng As Range, oName As Range, oProduct As Range
Dim i As Long, j As Long
Set oSource = ActiveDocument
Set oTable = oSource.Tables(1)
Set oTarget = Documents.Add
Set oData = oTarget.Range.Tables.Add(oTarget.Range, 1, 2)
oData.Cell(1, 1).Range.Text = "Customer"
oData.Cell(1, 2).Range.Text = "Product"
For Each oRow In oTable.Rows
If oRow.Index 1 Then
For Each oCell In oRow.Range.Cells
If Len(oCell.Range) 2 Then
Set oRng = oCell.Range
oRng.End = oRng.End - 1
i = Val(oRng.Text)
j = oCell.ColumnIndex
Set oName = oRow.Cells(1).Range
oName.End = oName.End - 1
Set oProduct = oTable.Cell(1, j).Range
oProduct.End = oProduct.End - 1
For k = 1 To i
Set oNewRow = oData.Rows.Add
oNewRow.Cells(1).Range.Text = oName.Text
oNewRow.Cells(2).Range.Text = oProduct.Text
Next k
End If
Next oCell
End If
Next oRow
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products to
be ordered in columns B-Z. Most cells are empty, but if someone orders a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and
print
as many labels as there are products ordered, for example, 2 labels for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

Sorry, I should have specified that the data source is an Excel spreadsheet.

I would like the labels to read as follows for my example, 1 label for each
product ordered so that I can stick a label on each product to make sorting
for delivery go more efficiently.

Something that I hadn't thought about before, but which would be helpful,
too, is to include "1 of 3" or something like that on the label where there
were multiples ordered.

Thanks for helping with this -- Terry

________________________________

John Doe
Jelly

John Doe
Jelly

John Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Peanuts

__________________________________


"Doug Robbins - Word MVP" wrote:

What is actually to be printed on the labels? The name of the product?
What is the data source?

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products to
be ordered in columns B-Z. Most cells are empty, but if someone orders a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and
print
as many labels as there are products ordered, for example, 2 labels for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

Thanks for helping with this -- The data source is actually an Excel
spreadsheet, which I should have specified in my original post -- sorry.

I assume that would significantly change how to accomplish what I want to
do. In my response to D. Robbins, I give an example of what I want to print
in each label, because I may not have made that clear either:
_____

John Doe
Jelly

John Doe
Jelly

John Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Peanuts

_____

In addition, it occurred to me that it would be helpful to include "1 of 3"
or something like that on the label where there were multiples ordered.

Would a modified version of the macro that you provided work on the Excel
data source?

Thanks again,
Terry


"Graham Mayor" wrote:

I take it this order form is a Word document with a single table? In which
case you can create a merge data source from that form, with which you can
create a label merge
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm . The following
macro will do that for however many columns you have.

Sub LabelData()
Dim oSource As Document
Dim oTarget As Document
Dim oTable As Table, oData As Table
Dim oRow As Row, oNewRow As Row
Dim oCell As Cell
Dim oRng As Range, oName As Range, oProduct As Range
Dim i As Long, j As Long
Set oSource = ActiveDocument
Set oTable = oSource.Tables(1)
Set oTarget = Documents.Add
Set oData = oTarget.Range.Tables.Add(oTarget.Range, 1, 2)
oData.Cell(1, 1).Range.Text = "Customer"
oData.Cell(1, 2).Range.Text = "Product"
For Each oRow In oTable.Rows
If oRow.Index 1 Then
For Each oCell In oRow.Range.Cells
If Len(oCell.Range) 2 Then
Set oRng = oCell.Range
oRng.End = oRng.End - 1
i = Val(oRng.Text)
j = oCell.ColumnIndex
Set oName = oRow.Cells(1).Range
oName.End = oName.End - 1
Set oProduct = oTable.Cell(1, j).Range
oProduct.End = oProduct.End - 1
For k = 1 To i
Set oNewRow = oData.Rows.Add
oNewRow.Cells(1).Range.Text = oName.Text
oNewRow.Cells(2).Range.Text = oProduct.Text
Next k
End If
Next oCell
End If
Next oRow
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products to
be ordered in columns B-Z. Most cells are empty, but if someone orders a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and
print
as many labels as there are products ordered, for example, 2 labels for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.


.

If you attach your data source to a Letter type mail merge main document
(there is no need to insert any merge fields into the document) and then run
a macro containing the following code:

Dim xlApp As Object
Dim xlbook As Object
Dim numrecs As Long, numflds As Long
Dim dSource As String
Dim qryStr As String
Dim i As Long, j As Long, k As Long
Dim Excelwasnotrunning As Boolean
Dim Customer As String
Dim Numitems As Long
Dim newsource As Document
Dim dtable As Table
Dim drow As Row
With ActiveDocument.MailMerge
If .MainDocumentType wdFormLetters Then
MsgBox "This application is only designed to be run with a
Letter type mail merge main document", _
vbCritical, "Not the correct type of mail merge document."
Exit Sub
End If
If Len(.DataSource.Name) = 0 Then
MsgBox "Attach the data source and re-run this macro", _
vbCritical, "No Data Source!"
Exit Sub
End If
If Right(.DataSource.Name, 4) "xlsx" And Right(.DataSource.Name,
3) "xls" Then
MsgBox "The data source must be an Excel Spreadsheet.", _
vbCritical, "Incorrect Data Source Format!"
Exit Sub
End If
End With
On Error GoTo ErrMsg
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
numrecs = .RecordCount
numflds = .FieldNames.Count
End With
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
qryStr = Mid(qryStr, InStr(qryStr, "`") + 1)
qryStr = Left(qryStr, Len(qryStr) - 2)
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Excelwasnotrunning = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
Set xlbook = xlApp.Workbooks.Open(dSource)
Set newsource = Documents.Add
Set dtable = newsource.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=3)
With dtable
.Cell(1, 1).Range.Text = "customer"
.Cell(1, 2).Range.Text = "Item"
.Cell(1, 3).Range.Text = "ItemCount"
For i = 1 To numrecs
Customer = xlbook.worksheets(qryStr).Cells(i + 1, 1).Value
For j = 2 To numflds
If xlbook.worksheets(qryStr).Cells(i + 1, j).Value ""
Then
Numitems = xlbook.worksheets(qryStr).Cells(i + 1,
j).Value
For k = 1 To Numitems
Set drow = .Rows.Add
With drow
.Cells(1).Range.Text = Customer
.Cells(2).Range.Text =
xlbook.worksheets(qryStr).Cells(1, j).Value
.Cells(3).Range.Text = k & " of " & Numitems
End With
Next k
End If
Next j
Next i
End With
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
If Excelwasnotrunning Then
xlApp.Quit
End If
Set xlApp = Nothing
ErrMsg:
If Err.Number 0 Then
MsgBox Err.Number & vbCr & Err.Description
Exit Sub
End If

It will create a new document containing a table like the following:

customer Item ItemCount
John Doe Jelly 1 of 2
John Doe Jelly 2 of 2
John Doe Jam 1 of 1
Jane Doe Jam 1 of 3
Jane Doe Jam 2 of 3
Jane Doe Jam 3 of 3
Jane Doe Peanuts 1 of 1

You can then save this document and use it as the data source for creating
your labels.

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
Sorry, I should have specified that the data source is an Excel
spreadsheet.

I would like the labels to read as follows for my example, 1 label for
each
product ordered so that I can stick a label on each product to make
sorting
for delivery go more efficiently.

Something that I hadn't thought about before, but which would be helpful,
too, is to include "1 of 3" or something like that on the label where
there
were multiples ordered.

Thanks for helping with this -- Terry

________________________________

John Doe
Jelly

John Doe
Jelly

John Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Peanuts

__________________________________


"Doug Robbins - Word MVP" wrote:

What is actually to be printed on the labels? The name of the product?
What is the data source?

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products
to
be ordered in columns B-Z. Most cells are empty, but if someone orders
a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and
print
as many labels as there are products ordered, for example, 2 labels for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

Doug,

Thank you for the coding that you've provided -- I'm getting closer with
this, but here's what I've got at this point:

1) I inserted your code into a macro with Sub/End Sub around it.

2) I opened a new Word doc and start a Letter mail merge, identifying the
data source as the Excel spreadsheet with the order information.

3) It runs to the point where it creates a new Word doc with a table that
has three columns with the headings "customer," "Item," and "ItemCount."

4) It stops and I get an error message: "9 Subscript out of range".

Examples of customer names are "Andrews, Lori" and "Arneson, Wendy".

Examples of item names are "KK 19 B&B Pickles" and "JA 32 Baby Pickled
Beets".

Do you have any thoughts on why it's stopping?

Many thanks,
Terry


"Doug Robbins - Word MVP" wrote:

If you attach your data source to a Letter type mail merge main document
(there is no need to insert any merge fields into the document) and then run
a macro containing the following code:

Dim xlApp As Object
Dim xlbook As Object
Dim numrecs As Long, numflds As Long
Dim dSource As String
Dim qryStr As String
Dim i As Long, j As Long, k As Long
Dim Excelwasnotrunning As Boolean
Dim Customer As String
Dim Numitems As Long
Dim newsource As Document
Dim dtable As Table
Dim drow As Row
With ActiveDocument.MailMerge
If .MainDocumentType wdFormLetters Then
MsgBox "This application is only designed to be run with a
Letter type mail merge main document", _
vbCritical, "Not the correct type of mail merge document."
Exit Sub
End If
If Len(.DataSource.Name) = 0 Then
MsgBox "Attach the data source and re-run this macro", _
vbCritical, "No Data Source!"
Exit Sub
End If
If Right(.DataSource.Name, 4) "xlsx" And Right(.DataSource.Name,
3) "xls" Then
MsgBox "The data source must be an Excel Spreadsheet.", _
vbCritical, "Incorrect Data Source Format!"
Exit Sub
End If
End With
On Error GoTo ErrMsg
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
numrecs = .RecordCount
numflds = .FieldNames.Count
End With
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
qryStr = Mid(qryStr, InStr(qryStr, "`") + 1)
qryStr = Left(qryStr, Len(qryStr) - 2)
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Excelwasnotrunning = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
Set xlbook = xlApp.Workbooks.Open(dSource)
Set newsource = Documents.Add
Set dtable = newsource.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=3)
With dtable
.Cell(1, 1).Range.Text = "customer"
.Cell(1, 2).Range.Text = "Item"
.Cell(1, 3).Range.Text = "ItemCount"
For i = 1 To numrecs
Customer = xlbook.worksheets(qryStr).Cells(i + 1, 1).Value
For j = 2 To numflds
If xlbook.worksheets(qryStr).Cells(i + 1, j).Value ""
Then
Numitems = xlbook.worksheets(qryStr).Cells(i + 1,
j).Value
For k = 1 To Numitems
Set drow = .Rows.Add
With drow
.Cells(1).Range.Text = Customer
.Cells(2).Range.Text =
xlbook.worksheets(qryStr).Cells(1, j).Value
.Cells(3).Range.Text = k & " of " & Numitems
End With
Next k
End If
Next j
Next i
End With
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
If Excelwasnotrunning Then
xlApp.Quit
End If
Set xlApp = Nothing
ErrMsg:
If Err.Number 0 Then
MsgBox Err.Number & vbCr & Err.Description
Exit Sub
End If

It will create a new document containing a table like the following:

customer Item ItemCount
John Doe Jelly 1 of 2
John Doe Jelly 2 of 2
John Doe Jam 1 of 1
Jane Doe Jam 1 of 3
Jane Doe Jam 2 of 3
Jane Doe Jam 3 of 3
Jane Doe Peanuts 1 of 1

You can then save this document and use it as the data source for creating
your labels.

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
Sorry, I should have specified that the data source is an Excel
spreadsheet.

I would like the labels to read as follows for my example, 1 label for
each
product ordered so that I can stick a label on each product to make
sorting
for delivery go more efficiently.

Something that I hadn't thought about before, but which would be helpful,
too, is to include "1 of 3" or something like that on the label where
there
were multiples ordered.

Thanks for helping with this -- Terry

________________________________

John Doe
Jelly

John Doe
Jelly

John Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Peanuts

__________________________________


"Doug Robbins - Word MVP" wrote:

What is actually to be printed on the labels? The name of the product?
What is the data source?

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and products
to
be ordered in columns B-Z. Most cells are empty, but if someone orders
a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product, and
print
as many labels as there are products ordered, for example, 2 labels for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1 label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

Hi Terry,

Can you send me a copy of your spreadsheet so that I can try and find out
what is causing the error.

The code I posted worked fine with a spreadsheet containing

Customer Jelly Jam Peanuts
John Doe 2 1Jane
Doe 3 1

Send it to


--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
Doug,

Thank you for the coding that you've provided -- I'm getting closer with
this, but here's what I've got at this point:

1) I inserted your code into a macro with Sub/End Sub around it.

2) I opened a new Word doc and start a Letter mail merge, identifying the
data source as the Excel spreadsheet with the order information.

3) It runs to the point where it creates a new Word doc with a table that
has three columns with the headings "customer," "Item," and "ItemCount."

4) It stops and I get an error message: "9 Subscript out of range".

Examples of customer names are "Andrews, Lori" and "Arneson, Wendy".

Examples of item names are "KK 19 B&B Pickles" and "JA 32 Baby Pickled
Beets".

Do you have any thoughts on why it's stopping?

Many thanks,
Terry


"Doug Robbins - Word MVP" wrote:

If you attach your data source to a Letter type mail merge main document
(there is no need to insert any merge fields into the document) and then
run
a macro containing the following code:

Dim xlApp As Object
Dim xlbook As Object
Dim numrecs As Long, numflds As Long
Dim dSource As String
Dim qryStr As String
Dim i As Long, j As Long, k As Long
Dim Excelwasnotrunning As Boolean
Dim Customer As String
Dim Numitems As Long
Dim newsource As Document
Dim dtable As Table
Dim drow As Row
With ActiveDocument.MailMerge
If .MainDocumentType wdFormLetters Then
MsgBox "This application is only designed to be run with a
Letter type mail merge main document", _
vbCritical, "Not the correct type of mail merge document."
Exit Sub
End If
If Len(.DataSource.Name) = 0 Then
MsgBox "Attach the data source and re-run this macro", _
vbCritical, "No Data Source!"
Exit Sub
End If
If Right(.DataSource.Name, 4) "xlsx" And
Right(.DataSource.Name,
3) "xls" Then
MsgBox "The data source must be an Excel Spreadsheet.", _
vbCritical, "Incorrect Data Source Format!"
Exit Sub
End If
End With
On Error GoTo ErrMsg
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
numrecs = .RecordCount
numflds = .FieldNames.Count
End With
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
qryStr = Mid(qryStr, InStr(qryStr, "`") + 1)
qryStr = Left(qryStr, Len(qryStr) - 2)
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Excelwasnotrunning = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
Set xlbook = xlApp.Workbooks.Open(dSource)
Set newsource = Documents.Add
Set dtable = newsource.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=3)
With dtable
.Cell(1, 1).Range.Text = "customer"
.Cell(1, 2).Range.Text = "Item"
.Cell(1, 3).Range.Text = "ItemCount"
For i = 1 To numrecs
Customer = xlbook.worksheets(qryStr).Cells(i + 1, 1).Value
For j = 2 To numflds
If xlbook.worksheets(qryStr).Cells(i + 1, j).Value ""
Then
Numitems = xlbook.worksheets(qryStr).Cells(i + 1,
j).Value
For k = 1 To Numitems
Set drow = .Rows.Add
With drow
.Cells(1).Range.Text = Customer
.Cells(2).Range.Text =
xlbook.worksheets(qryStr).Cells(1, j).Value
.Cells(3).Range.Text = k & " of " & Numitems
End With
Next k
End If
Next j
Next i
End With
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
If Excelwasnotrunning Then
xlApp.Quit
End If
Set xlApp = Nothing
ErrMsg:
If Err.Number 0 Then
MsgBox Err.Number & vbCr & Err.Description
Exit Sub
End If

It will create a new document containing a table like the following:

customer Item ItemCount
John Doe Jelly 1 of 2
John Doe Jelly 2 of 2
John Doe Jam 1 of 1
Jane Doe Jam 1 of 3
Jane Doe Jam 2 of 3
Jane Doe Jam 3 of 3
Jane Doe Peanuts 1 of 1

You can then save this document and use it as the data source for
creating
your labels.

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
Sorry, I should have specified that the data source is an Excel
spreadsheet.

I would like the labels to read as follows for my example, 1 label for
each
product ordered so that I can stick a label on each product to make
sorting
for delivery go more efficiently.

Something that I hadn't thought about before, but which would be
helpful,
too, is to include "1 of 3" or something like that on the label where
there
were multiples ordered.

Thanks for helping with this -- Terry

________________________________

John Doe
Jelly

John Doe
Jelly

John Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Peanuts

__________________________________


"Doug Robbins - Word MVP" wrote:

What is actually to be printed on the labels? The name of the
product?
What is the data source?

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and
products
to
be ordered in columns B-Z. Most cells are empty, but if someone
orders
a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product,
and
print
as many labels as there are products ordered, for example, 2 labels
for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1
label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.

Thanks, Doug!

I sure appreciate all the help you gave me. Your code, of course, was right
from the start -- it was my spreadsheet that had errors in it that stopped
the macro from executing properly until you showed me how to clean it up.

Thank you so much!
Terry

"Doug Robbins - Word MVP" wrote:

Hi Terry,

Can you send me a copy of your spreadsheet so that I can try and find out
what is causing the error.

The code I posted worked fine with a spreadsheet containing

Customer Jelly Jam Peanuts
John Doe 2 1Jane
Doe 3 1

Send it to


--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
Doug,

Thank you for the coding that you've provided -- I'm getting closer with
this, but here's what I've got at this point:

1) I inserted your code into a macro with Sub/End Sub around it.

2) I opened a new Word doc and start a Letter mail merge, identifying the
data source as the Excel spreadsheet with the order information.

3) It runs to the point where it creates a new Word doc with a table that
has three columns with the headings "customer," "Item," and "ItemCount."

4) It stops and I get an error message: "9 Subscript out of range".

Examples of customer names are "Andrews, Lori" and "Arneson, Wendy".

Examples of item names are "KK 19 B&B Pickles" and "JA 32 Baby Pickled
Beets".

Do you have any thoughts on why it's stopping?

Many thanks,
Terry


"Doug Robbins - Word MVP" wrote:

If you attach your data source to a Letter type mail merge main document
(there is no need to insert any merge fields into the document) and then
run
a macro containing the following code:

Dim xlApp As Object
Dim xlbook As Object
Dim numrecs As Long, numflds As Long
Dim dSource As String
Dim qryStr As String
Dim i As Long, j As Long, k As Long
Dim Excelwasnotrunning As Boolean
Dim Customer As String
Dim Numitems As Long
Dim newsource As Document
Dim dtable As Table
Dim drow As Row
With ActiveDocument.MailMerge
If .MainDocumentType wdFormLetters Then
MsgBox "This application is only designed to be run with a
Letter type mail merge main document", _
vbCritical, "Not the correct type of mail merge document."
Exit Sub
End If
If Len(.DataSource.Name) = 0 Then
MsgBox "Attach the data source and re-run this macro", _
vbCritical, "No Data Source!"
Exit Sub
End If
If Right(.DataSource.Name, 4) "xlsx" And
Right(.DataSource.Name,
3) "xls" Then
MsgBox "The data source must be an Excel Spreadsheet.", _
vbCritical, "Incorrect Data Source Format!"
Exit Sub
End If
End With
On Error GoTo ErrMsg
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
numrecs = .RecordCount
numflds = .FieldNames.Count
End With
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
qryStr = Mid(qryStr, InStr(qryStr, "`") + 1)
qryStr = Left(qryStr, Len(qryStr) - 2)
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Excelwasnotrunning = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
Set xlbook = xlApp.Workbooks.Open(dSource)
Set newsource = Documents.Add
Set dtable = newsource.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=3)
With dtable
.Cell(1, 1).Range.Text = "customer"
.Cell(1, 2).Range.Text = "Item"
.Cell(1, 3).Range.Text = "ItemCount"
For i = 1 To numrecs
Customer = xlbook.worksheets(qryStr).Cells(i + 1, 1).Value
For j = 2 To numflds
If xlbook.worksheets(qryStr).Cells(i + 1, j).Value ""
Then
Numitems = xlbook.worksheets(qryStr).Cells(i + 1,
j).Value
For k = 1 To Numitems
Set drow = .Rows.Add
With drow
.Cells(1).Range.Text = Customer
.Cells(2).Range.Text =
xlbook.worksheets(qryStr).Cells(1, j).Value
.Cells(3).Range.Text = k & " of " & Numitems
End With
Next k
End If
Next j
Next i
End With
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
If Excelwasnotrunning Then
xlApp.Quit
End If
Set xlApp = Nothing
ErrMsg:
If Err.Number 0 Then
MsgBox Err.Number & vbCr & Err.Description
Exit Sub
End If

It will create a new document containing a table like the following:

customer Item ItemCount
John Doe Jelly 1 of 2
John Doe Jelly 2 of 2
John Doe Jam 1 of 1
Jane Doe Jam 1 of 3
Jane Doe Jam 2 of 3
Jane Doe Jam 3 of 3
Jane Doe Peanuts 1 of 1

You can then save this document and use it as the data source for
creating
your labels.

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
Sorry, I should have specified that the data source is an Excel
spreadsheet.

I would like the labels to read as follows for my example, 1 label for
each
product ordered so that I can stick a label on each product to make
sorting
for delivery go more efficiently.

Something that I hadn't thought about before, but which would be
helpful,
too, is to include "1 of 3" or something like that on the label where
there
were multiples ordered.

Thanks for helping with this -- Terry

________________________________

John Doe
Jelly

John Doe
Jelly

John Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Jam

Jane Doe
Peanuts

__________________________________


"Doug Robbins - Word MVP" wrote:

What is actually to be printed on the labels? The name of the
product?
What is the data source?

--
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, originally posted via msnews.microsoft.com

"TThunder" wrote in message
...
I'm using Office 2003 --

I have an order form with the purchaser's name in column A and
products
to
be ordered in columns B-Z. Most cells are empty, but if someone
orders
a
product, the number of items ordered for each product is in the
corresponding
cell.

Example:

Customer Jelly Jam Peanuts

John Doe 2 1

Jane Doe 3 1

I would like to print labels with the customer's name and product,
and
print
as many labels as there are products ordered, for example, 2 labels
for
John
Doe/Jelly, 1 label for John Doe/Jam, 3 labels for Jane Doe/Jam, 1
label
for
Jane Doe/Peanuts, etc.

How can I do this? Thanks -- T.