Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in dbase
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. -- Thanks, Joe S |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in dbase
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 "joes" wrote in message ... 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. -- Thanks, Joe S |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in db
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. "Doug Robbins - Word MVP" wrote: 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 "joes" wrote in message ... 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. -- Thanks, Joe S |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in db
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 "ebuyitnet" wrote in message ... 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. "Doug Robbins - Word MVP" wrote: 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 "joes" wrote in message ... 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. -- Thanks, Joe S |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in db
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/mic...f?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 Befo=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 ebuyitnet wrote: 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. "Doug Robbins - Word MVP" wrote: 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 "joes" wrote in message ... 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. -- Thanks, Joe S |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in db
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" wrote: 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/mic...f?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 Befo=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 ebuyitnet wrote: 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. "Doug Robbins - Word MVP" wrote: 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 "joes" wrote in message ... 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. -- Thanks, Joe S |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Can I print designated number of label copies based on # in db
Always useful to know when something works - thanks for the feedback.
Peter Jamieson http://tips.pjmsn.me.uk ebuyitnet wrote: 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" wrote: 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/mic...f?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 Befo=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 ebuyitnet wrote: 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. "Doug Robbins - Word MVP" wrote: 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 "joes" wrote in message ... 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. -- Thanks, Joe S |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when you print more than 1 copy, how to number the copies | Microsoft Word Help | |||
I want to print 50 copies of a form each with a different number | Microsoft Word Help | |||
Mail merge outlook as contact dbase. Datalink properties dbase | Mailmerge | |||
Restoring "Number of copies" print default? | Microsoft Word Help | |||
Number * of 150 copies in a label | Tables |