[TThunder]

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.