Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Nit_Wit_400 Nit_Wit_400 is offline
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Nit_Wit_400 Nit_Wit_400 is offline
external usenet poster
 
Posts: 11
Default Repeat the fields

Sorry; forgot to mention I'm merging into Word 2003 and Excel 2003 is
my database.
  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Nit_Wit_400 Nit_Wit_400 is offline
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Nit_Wit_400 Nit_Wit_400 is offline
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Nit_Wit_400 Nit_Wit_400 is offline
external usenet poster
 
Posts: 11
Default 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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default 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

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I define text, and then repeat it using fields? Erich_von_Pica Page Layout 1 May 8th 09 10:10 PM
how do I repeat the same merge fields on one page Jan Mailmerge 6 February 11th 09 04:25 PM
Is it possible to get fields to repeat? LisaK Microsoft Word Help 3 October 3rd 08 05:52 PM
Repeat Fill-in Fields Maggie Northfield Microsoft Word Help 1 November 21st 07 10:17 PM
I want fields to repeat - please help! Narowr Mailmerge 2 May 12th 06 05:50 AM


All times are GMT +1. The time now is 10:10 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"