Using data from all worksheets within a workbook for mail merge

[Scott]

I am trying to merge data from all the worksheets in one Excel workbook into
a Word document using Office 2000. I have tried using the converter and
opening the workbook, but it will still only show the fields from the first
worksheet. Any help anyone can give would be greatly appreciated.

 

[Peter Jamieson]

You should be able to see all the sheets in the dropdown that "Entire
Workbook" appears in, but no connection method lets you /combine/ the
worksheets in a workbook into a single worksheet for the purposes of a
merge.

What you can do depends on what you are trying to combine.

If you have several sheets with exactly the same columns and you just want
to put all the rows into a single data source,
a. You /may/ be able to combine the worksheets using (say) an ODBC
connection, MS Query, and some SQL with UNION statements in it, but
generally speaking it is probably simpler and more reliable to
b. combine the data manually (or perhaps using a suitable Excel macro) in a
new Excel workbook, then use that as the data source for the merge.

If you have several sheets and row 2 on each sheet has different columns for
the same item, and you want to combine all the columns, then again, you
might be able to use SQL, but
a. it's still probably easier to combine "by hand" in Excel
b. you face a maximum of 255 columns. If you need more than that, there's
only one known option, which is to export to a delimited format, e.g. in a
text file.

Peter Jamieson

 

[Cindy M.]

Hi =?Utf-8?B?U2NvdHQ=?=,

I am trying to merge data from all the worksheets in one Excel workbook into
a Word document using Office 2000. I have tried using the converter and
opening the workbook, but it will still only show the fields from the first
worksheet.

This isn't something you can do without some outside help, in the form of MS
Query. See this article

http://support.microsoft.com/kb/180599/en-us

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail