Mail merging with multiple worksheets

[Jo]

In Office 2000, when mail merging to data in Excel 2000 --
a workbook with several worksheets -- Word is not
recognizing the worksheets, just the entire workbook and
therefore is not merging properly. When I move target
worksheet to file/workbook of its own, it works properly.
Does anyone have any ideas on this? Please help, as I
don't want to have to split 40-odd worksheets out into
separate mail-merge sheets!

Thanks,
Jo

 

[Arvi Laanemets]

Hi

Try it by defining your merge tables as named ranges - it'll be the only
possible way I can think of at moment. (Don't forget to save the workbook
before linking it to mail merge document)

Otherwise the first sheet in workbook is linked - accordingly to MS Help -
but some times ago I had problem with this too.

 

[JoGosw]

How do I define the tables as named ranges? I've put the name of th
worksheet down on the named or cell range after opening the dat
source, but this does not seem to work (ie when I then try to put th
worksheet name down, I still only get the heading in the firs
worksheet as an insert merge field

 

[Arvi Laanemets]

Hi

When the table dimensions doesn't change in future, then select the whole
datarange including header row, and then Insert.Range.Define from menu.
Insert the name for range into Name field - the reference was put
automatically into refers to field.

When you will add new records into table in future, then the best way is to
define the range dynamically. An example:
You have a table in range A1:E100 (5 columns) on sheet Sheet1 at moment.
Create a range p.e. MyRange, and enter into 'Refers to' field the formula
=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A,"<>"),5)
Now, whenever you add a row to table, or delete a row, the named range
expands or shrinks accordingly. But you mustn't have any entries in column A
exept these belonging to table, and there mustn't be any empty rows in
table. I.e. any new entries must be inserted into row next to prior last
one, and when deleting entries in table you have to delete entire row.

 

[JoGosw]

Hi Arvi,

I'm sorry, but you've lost me. When do I do the Insert.Range.Defin
bit? When I'm setting up a mail merge (ie I've got the main document
I've opened the data sounce, and I now have the 'named or cell range
box come up), I have no way of doing an insert etc bit - I can onl
change 'Entire Spreadsheet' to something else (eg the name of th
worksheet I want), which doesn't work. (I don't have anything excep
'Entire Spreadsheet' in the highlight area of the box)

If I highlight the range in the Excel spreadsheet, and go to Insert i
the main menu, I don't have a range option. I tried doin
Insert.Name.Define, but when I tried to add in the name of th
worksheet, it came up as invalid.

Thanks for your help,
J

 

[Arvi Laanemets]

Hi

Hi Arvi,

I'm sorry, but you've lost me. When do I do the Insert.Range.Define
bit?

Sorry, my fault! Insert.Name.Define
(It's from Excel)

When I'm setting up a mail merge (ie I've got the main document,
I've opened the data sounce, and I now have the 'named or cell range'
box come up), I have no way of doing an insert etc bit - I can only
change 'Entire Spreadsheet' to something else (eg the name of the
worksheet I want), which doesn't work. (I don't have anything except
'Entire Spreadsheet' in the highlight area of the box)

If I highlight the range in the Excel spreadsheet, and go to Insert in
the main menu, I don't have a range option. I tried doing
Insert.Name.Define, but when I tried to add in the name of the
worksheet, it came up as invalid.

Select the whole (headers included) range where your source table is stored.
With selection still active, select Insert.Name.Define from menu, and enter
the range name into field at top. In RefersTo field you have now something
like
=YourSheet!$A$1:$E$100
or whatever your datarange is. Press OK, and save the workbook. Now try to
connect to worksheet as datasource for Mail Merge in your mail merge
document - when you are lucky, then you can select the freshly defined
range.