Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Hello all.
Is there any way to set up my mail merge fields to stay whenever I merge from a database. For instance, I have multiple databases which are all set up the same way, and I need to merge them into one word document with the same repeating format. For example: ((DB1)) Nottle: 36,375.0-125.J-00-000 and 15.23 acres, abstract no. 0511092101-02; ((DB2)) Nottle: 36,375.0-125.C-00-000 and 15.24 acres, abstract no. 0511092101-06; ((DB3)) Hons: 36,375.0-125.A-00-000 and 1.09 acres, abstract no. 0511092101-07; ((DB4)) Herman: 36,375.0-125.B-00-000 and 7.30 acres, abstract no. 0511092101-01; ((DB5)) DiGian: 36,375.0-125.F-00-000 and 17.17 acres, abstract no. 0511092101-04; ((DB6)) DiGian: 36,375.0-125-E-00-000 and 17.70 acres, abstract no. 0511092101-03; So on and so forth. The fields being Last Name: Number and Number acres, abstract no. Number; If there is any way to make those fields repeat like that without having to copy-paste them, or whatever, please help me out? |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Sorry; forgot to mention I'm merging into Word 2003 and Excel 2003 is
my database. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Hi Nit_Wit_400,
A Word mailmerge main document can have only one data source, so what you want to do isn't possible in the way you want to do it. You could try running a db query to extract the data from each database into a single table that Word could use as its mailmerge datasource. -- Cheers macropod [Microsoft MVP - Word] "Nit_Wit_400" wrote in message ... Hello all. Is there any way to set up my mail merge fields to stay whenever I merge from a database. For instance, I have multiple databases which are all set up the same way, and I need to merge them into one word document with the same repeating format. For example: ((DB1)) Nottle: 36,375.0-125.J-00-000 and 15.23 acres, abstract no. 0511092101-02; ((DB2)) Nottle: 36,375.0-125.C-00-000 and 15.24 acres, abstract no. 0511092101-06; ((DB3)) Hons: 36,375.0-125.A-00-000 and 1.09 acres, abstract no. 0511092101-07; ((DB4)) Herman: 36,375.0-125.B-00-000 and 7.30 acres, abstract no. 0511092101-01; ((DB5)) DiGian: 36,375.0-125.F-00-000 and 17.17 acres, abstract no. 0511092101-04; ((DB6)) DiGian: 36,375.0-125-E-00-000 and 17.70 acres, abstract no. 0511092101-03; So on and so forth. The fields being Last Name: Number and Number acres, abstract no. Number; If there is any way to make those fields repeat like that without having to copy-paste them, or whatever, please help me out? |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Are your separate databases in separate workbooks, or are they in the
same workbook but in different worksheets? Does each database have the same column names and sequence of columns /in the data source/ ? What sequence of records do you need in your output document (i.e. do you need the first record from each data source, followed by the second record from each data source, etc. When one data source runs out of records, do you any kind of placeholder or do you just want to continue with the next available record? (FWIW the simplest approach is likely to be to use Excel formulas or Excel VBA code to gather all the records into a single worksheet). Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Nit_Wit_400 wrote: Sorry; forgot to mention I'm merging into Word 2003 and Excel 2003 is my database. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Mr. Jamieson,
Are your separate databases in separate workbooks, or are they in the same workbook but in different worksheets? Yes, my data is in separate excel spreadsheets. Does each database have the same column names and sequence of columns /in the data source/ ? Yes, each file is exactly the same, except for the information under the headers. In actuality, I have a header row for which each of that row's cell's column contains information from the spreadsheet. For example: Row 1 = "1"....."98" Row 2 = B8....B98 Col A = Header column for col B It is done this way so that the word document says F1 instead of Client Name .. can't say I like it, but that's the way chain of command works around here. I'm told to do something and I have to do it whether I like the way it is done or not . What sequence of records do you need in your output document (i.e. do you need the first record from each data source, followed by the second record from each data source, etc. When one data source runs out of records, do you any kind of placeholder or do you just want to continue with the next available record? Each spreadsheet has the data for each client, however, in the Word document, we need to combine the information into one. Ideally, it needs to handle X amount of input and it needs to be in the format from my last posting. I'm not sure what you mean by my data source running out of records because the mergefield wouldn't be there if there wasn't going to be information to put in it, but I guess if for some reason the cell was erroneously empty, an error message should appear. However, I guess in some cases, there would be an instance when there was no information given to us about the cell's required information. In this case, it should either be skipped, or if it is purposely empty, it usually means that a whole section of the word document should be eliminated. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
You'll need a macro in this case - I suggest you look at
http://msdn.microsoft.com/en-us/library/cc793964.aspx which has some examples with instructions on how to adapt the code for various situations. If you need help with that, post back. However, if you need to do that I could use some clarification about the format of your worksheets. I'm told to do something and I have to do it whether I like the way it is done or not . I'm familiar with that set-up. Yes, each file is exactly the same, except for the information under the headers. In actuality, I have a header row for which each of that row's cell's column contains information from the spreadsheet. For example: Row 1 = "1"....."98" Row 2 = B8....B98 Col A = Header column for col B I didn't really understand the details you have here and wonder if there is a typo. Perhaps an example that shows what the first 5 rows of one of the sheets looks like would help. I'm not sure what you mean by my data source running out of records What I meant was this - your example of the output shows 6 records, each one from a different workbook. To simplify, suppose you have 3 workbooks A,B, and C with 8, 6 and 4 records respectively. If "A1" means the first row of data in workbook A, should the output be in this sequence: A1 B1 C1 A2 B2 C2 A3 B3 C3 A4 B4 C4 A5 B5 A6 B6 A7 A8 or what (it could be for example that you needs blocks of three like this A1 B1 C1 A2 B2 C2 A3 B3 C3 A4 B4 C4 A5 B5 empty row A6 B6 empty row A7 empty row empty row A8 empty row empty row ) Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Nit_Wit_400 wrote: Mr. Jamieson, Are your separate databases in separate workbooks, or are they in the same workbook but in different worksheets? Yes, my data is in separate excel spreadsheets. Does each database have the same column names and sequence of columns /in the data source/ ? Yes, each file is exactly the same, except for the information under the headers. In actuality, I have a header row for which each of that row's cell's column contains information from the spreadsheet. For example: Row 1 = "1"....."98" Row 2 = B8....B98 Col A = Header column for col B It is done this way so that the word document says F1 instead of Client Name .. can't say I like it, but that's the way chain of command works around here. I'm told to do something and I have to do it whether I like the way it is done or not . What sequence of records do you need in your output document (i.e. do you need the first record from each data source, followed by the second record from each data source, etc. When one data source runs out of records, do you any kind of placeholder or do you just want to continue with the next available record? Each spreadsheet has the data for each client, however, in the Word document, we need to combine the information into one. Ideally, it needs to handle X amount of input and it needs to be in the format from my last posting. I'm not sure what you mean by my data source running out of records because the mergefield wouldn't be there if there wasn't going to be information to put in it, but I guess if for some reason the cell was erroneously empty, an error message should appear. However, I guess in some cases, there would be an instance when there was no information given to us about the cell's required information. In this case, it should either be skipped, or if it is purposely empty, it usually means that a whole section of the word document should be eliminated. |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Mr. Jamieson
Sorry for taking so long to reply; I got caught up in other work stuff. But alas, I'm back to this particular grindstone. Actually, I've made some headway. I've figured out an easy-ish way which will theoretically solve the issue. It will still require some grunt-work, but it will cut out some serious time in the end-product. So, in my workbook, I have a header row which refers to the information in column B: Row 1 is numbered 1-99 Row 2 =B8:B106 meaning A2 thru CU2 is equal to B8 thru B106 -- know what I mean? Row 3 has information for the dropdown menus throughout the spreadsheet. This is done to prevent erroneous entries in some specific cells -- some of our analysts are daft. For some unholy reason, A4:6 thru C4:6 are merged together to make the title cell of the spreadsheet (don't know why whoever made the template just didn't use one cell... but whatever, that's the way it is). The first 3 rows are hidden so that the analyst can't tamper with the spreadsheet. Anyway, row 7 is blank (to separate the meat from the title). The rest of Column A refers to what the analyst should put in the corresponding cells of column B. --------------above here is what is currently used as a mailmerge data source And until now, this worked fine, as long as the Word document only needed one set of information. Recent developments require that the Word document contains up to (and hopefully no more than) 10 sets. So, my idea is to make 9 more header rows which refer to columns C thru J if you catch my drift. Think that'll work? |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
If you are just adding rows (and columns) then I don't think you are
introducing any new problems, except that you will need to filter out the additional "header" rows. Personally, I'd add in the extra row/column and see what happens. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Nit_Wit_400 wrote: Mr. Jamieson Sorry for taking so long to reply; I got caught up in other work stuff. But alas, I'm back to this particular grindstone. Actually, I've made some headway. I've figured out an easy-ish way which will theoretically solve the issue. It will still require some grunt-work, but it will cut out some serious time in the end-product. So, in my workbook, I have a header row which refers to the information in column B: Row 1 is numbered 1-99 Row 2 =B8:B106 meaning A2 thru CU2 is equal to B8 thru B106 -- know what I mean? Row 3 has information for the dropdown menus throughout the spreadsheet. This is done to prevent erroneous entries in some specific cells -- some of our analysts are daft. For some unholy reason, A4:6 thru C4:6 are merged together to make the title cell of the spreadsheet (don't know why whoever made the template just didn't use one cell... but whatever, that's the way it is). The first 3 rows are hidden so that the analyst can't tamper with the spreadsheet. Anyway, row 7 is blank (to separate the meat from the title). The rest of Column A refers to what the analyst should put in the corresponding cells of column B. --------------above here is what is currently used as a mailmerge data source And until now, this worked fine, as long as the Word document only needed one set of information. Recent developments require that the Word document contains up to (and hopefully no more than) 10 sets. So, my idea is to make 9 more header rows which refer to columns C thru J if you catch my drift. Think that'll work? |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
So I've tried my idea out, and alas, it doesn't work.
Is there any way to say that there are multiple "header" rows. I want to be able to just use a merge field in the document and have it pull it from any one of the header rows... For example: A1 is Client 1's last name and it comes from the first "header row" which refers to the information in column B B1 is Client 2's last name and it comes from the second "header row" which refers to the information in column C so on and so forth. My other idea was to just put all my "header rows" in one row... but the maximum columns allowed only goes to IV which is only 230 columns and I need upwards of 1000 which is why I originally thought I should break it up into different "header rows." |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Repeat the fields
Sorry for the delayed reply.
Is there any way to say that there are multiple "header" rows. I want to be able to just use a merge field in the document and have it pull it from any one of the header rows... For example: Not in the same Mail Merge Main document (you could do it in different mail merge main documents by specifying a named range that started with the row you wanted to use) My other idea was to just put all my "header rows" in one row... but the maximum columns allowed only goes to IV which is only 230 columns and I need upwards of 1000 which is why I originally thought I should break it up into different "header rows." Word MailMerge can't see more than 255 columns, even in Office 2007 where Excel can contain many more columns than that. I can't help feeling that an approach that gets this complicated is the wrong one, regardless of whatever standards you have to work within. Because it's complicated, I always get the feeling that I simply haven't understood what you are trying to achieve and why it has to be done this way. I'll have another look when I can... Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Nit_Wit_400 wrote: So I've tried my idea out, and alas, it doesn't work. Is there any way to say that there are multiple "header" rows. I want to be able to just use a merge field in the document and have it pull it from any one of the header rows... For example: A1 is Client 1's last name and it comes from the first "header row" which refers to the information in column B B1 is Client 2's last name and it comes from the second "header row" which refers to the information in column C so on and so forth. My other idea was to just put all my "header rows" in one row... but the maximum columns allowed only goes to IV which is only 230 columns and I need upwards of 1000 which is why I originally thought I should break it up into different "header rows." |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I define text, and then repeat it using fields? | Page Layout | |||
how do I repeat the same merge fields on one page | Mailmerge | |||
Is it possible to get fields to repeat? | Microsoft Word Help | |||
Repeat Fill-in Fields | Microsoft Word Help | |||
I want fields to repeat - please help! | Mailmerge |