| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
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 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. |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
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 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. |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
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 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. |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
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 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. . |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
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 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. |
|
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
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 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. |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
|
|
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Printing multiple labels based on contents of cell
|
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Import Excel Cell Contents Into Word Labels | New Users | |||
| Cell contents shift right when printing | Tables | |||
| printing multiple labels in Word 2007 | Microsoft Word Help | |||
| Multiple labels from one row in the data list based on a variable | Mailmerge | |||
| how do i make a list of contents based on multiple documents | Microsoft Word Help | |||
Linear Mode
