| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Printing Variable Label Quantities from a Merge
Greetings;
I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks |
|
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
I can export the database
What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks |
|
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
I can export the database
What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks |
|
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
It is an Excel file. And my plan was to use Word as the 'merger' ... I'm
looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . |
|
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
It is an Excel file. And my plan was to use Word as the 'merger' ... I'm
looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . |
|
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
If you have a worksheet with the layout I described in Sheet1, the
following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . |
|
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
If you have a worksheet with the layout I described in Sheet1, the
following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . |
|
#8
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
Access it is then ... I'll finally open that program for something!
THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . |
|
#9
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
Access it is then ... I'll finally open that program for something!
THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . |
|
#10
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
If you find the facilities you need in Access's Label Wizard, the other
piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . |
|
#11
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
If you find the facilities you need in Access's Label Wizard, the other
piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . |
|
#12
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
So it WILL be much easier to do it in Access (and yes I can just link it).
However, I am having an issue setting the command to "print 'x' number based on value in 'y' field". Thanks "Peter Jamieson" wrote: If you find the facilities you need in Access's Label Wizard, the other piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . . |
|
#13
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
So it WILL be much easier to do it in Access (and yes I can just link it).
However, I am having an issue setting the command to "print 'x' number based on value in 'y' field". Thanks "Peter Jamieson" wrote: If you find the facilities you need in Access's Label Wizard, the other piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . . |
|
#14
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
Umm, sorry, I really thought that particular wizard could do that
particular job but now I've looked again I can see that it cannot easily do so, at least not on its own. From this point, the simplest approach is probably either to use the Excel macro I provided, and link /that/ sheet into Access, or stick with Word if you do not want to get any further into Access. There is another possible approach that involves creation of either one more Excel sheet or Access table and a query, but at the moment I don't have the time to turn that into a workable solution for you. Peter Jamieson http://tips.pjmsn.me.uk On 21/02/2010 23:07, Wake Manor wrote: So it WILL be much easier to do it in Access (and yes I can just link it). However, I am having an issue setting the command to "print 'x' number based on value in 'y' field". Thanks "Peter Jamieson" wrote: If you find the facilities you need in Access's Label Wizard, the other piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . . |
|
#15
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
Umm, sorry, I really thought that particular wizard could do that
particular job but now I've looked again I can see that it cannot easily do so, at least not on its own. From this point, the simplest approach is probably either to use the Excel macro I provided, and link /that/ sheet into Access, or stick with Word if you do not want to get any further into Access. There is another possible approach that involves creation of either one more Excel sheet or Access table and a query, but at the moment I don't have the time to turn that into a workable solution for you. Peter Jamieson http://tips.pjmsn.me.uk On 21/02/2010 23:07, Wake Manor wrote: So it WILL be much easier to do it in Access (and yes I can just link it). However, I am having an issue setting the command to "print 'x' number based on value in 'y' field". Thanks "Peter Jamieson" wrote: If you find the facilities you need in Access's Label Wizard, the other piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . . |
|
#16
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
Thanks for your help anyhow - it was greatly appreciated.
"Peter Jamieson" wrote: Umm, sorry, I really thought that particular wizard could do that particular job but now I've looked again I can see that it cannot easily do so, at least not on its own. From this point, the simplest approach is probably either to use the Excel macro I provided, and link /that/ sheet into Access, or stick with Word if you do not want to get any further into Access. There is another possible approach that involves creation of either one more Excel sheet or Access table and a query, but at the moment I don't have the time to turn that into a workable solution for you. Peter Jamieson http://tips.pjmsn.me.uk On 21/02/2010 23:07, Wake Manor wrote: So it WILL be much easier to do it in Access (and yes I can just link it). However, I am having an issue setting the command to "print 'x' number based on value in 'y' field". Thanks "Peter Jamieson" wrote: If you find the facilities you need in Access's Label Wizard, the other piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . . . |
|
#17
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
|
Printing Variable Label Quantities from a Merge
Thanks for your help anyhow - it was greatly appreciated.
"Peter Jamieson" wrote: Umm, sorry, I really thought that particular wizard could do that particular job but now I've looked again I can see that it cannot easily do so, at least not on its own. From this point, the simplest approach is probably either to use the Excel macro I provided, and link /that/ sheet into Access, or stick with Word if you do not want to get any further into Access. There is another possible approach that involves creation of either one more Excel sheet or Access table and a query, but at the moment I don't have the time to turn that into a workable solution for you. Peter Jamieson http://tips.pjmsn.me.uk On 21/02/2010 23:07, Wake Manor wrote: So it WILL be much easier to do it in Access (and yes I can just link it). However, I am having an issue setting the command to "print 'x' number based on value in 'y' field". Thanks "Peter Jamieson" wrote: If you find the facilities you need in Access's Label Wizard, the other piece of info. you may find handy is that if you have a spreadsheet in a suitable format, you do not have to import it as a separate step since you can (or should be able to) create a link to that sheet instead. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 13:37, Wake Manor wrote: Access it is then ... I'll finally open that program for something! THANKS! "Peter Jamieson" wrote: If you have a worksheet with the layout I described in Sheet1, the following crude bit of Excel VBA code should delete any existing "LabelRows" sheet, duplicate Sheet1, creating the right number of rows in a new "LabelRows", which you should then be able to use as the merge data source. The sheet will be at the beginning of the workbook should you have to connect using DDE. Probably best to work with copies of your data. This code doesn't do any error checking to speak of, but should be OK if your label count doesn't exceed the maximum number of rows To do the whole thing from Word would require you either to integrate a modified version of this macro, or something similar, into a Word VBA macro. There are other ways that this problem could be approached, e.g. a. If you have Access, its label generator may be worth investigation b. there is another way to get Excel to return the right number of rows without Excel VBA, but it's quite nasty However, I think the above is straightforward and should certainly be a good enough starting point if it's a one-off. Peter Jamieson http://tips.pjmsn.me.uk On 20/02/2010 02:27, Wake Manor wrote: It is an Excel file. And my plan was to use Word as the 'merger' ... I'm looking at quantities from 1 - 250 - so the easier the better. Thanks for the response. "Peter Jamieson" wrote: I can export the database What type of database is it? (Excel workbook, access, some other database?) You may be able to do something directly in there. Otherwise, if a. you can get an export like this Product Quantity A 1 B 5 C 7 .. .. and you know the maximum Qty (let's say it is 10), then b. you can do this without having to resort to VBA:- 1. Create a directory (catalog) merge Put the following fields in it { IF { MERGEFIELD QUANTITY } 0 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 1 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 2 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 3 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 4 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 5 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 6 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 7 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 8 "{ MERGEFIELD PRODUCT } " }{ IF { MERGEFIELD QUANTITY } 9 "{ MERGEFIELD PRODUCT } " } NB, all the {} need to be the special field code braces you can enter using ctrl-F9 2. Merge to a new document. You should have one column containing the right number of rows for each PRODUCT. Manually insert the column header PRODUCT, save and close the document and use it as the data source for your label merge. Peter Jamieson http://tips.pjmsn.me.uk On 19/02/2010 22:53, Wake Manor wrote: Greetings; I am trying to figure out how to print variable quantities of labels based on a "quantity" field in a File. The purpose is to print out inventory labels. I can export the database which will have, for example, the following fields. Product: A B C Qty: 1 5 7 I can set up the basic merge (the Product line), but I want to be able to tell Word to print out ONE label of "A", FIVE labels of "B" and SEVEN labels of "C". Help please!!! Thanks . . . . |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In mail merge can I select which label to start with printing | Mailmerge | |||
| Label printing from excel mail merge | Mailmerge | |||
| printing a label mail merge | Mailmerge | |||
| Merge Label Printing | Microsoft Word Help | |||
| Limit quantities on a form template in Word | Microsoft Word Help | |||
Linear Mode
