[Doug Robbins - Word MVP]

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.