Mail merge in Word with Excel sheets as data source

[Tina-Jeanette Thorheim]

Hi,
I'm trying to mail merge labels with Excel sheets as data source. My data
source in Excel is large, aproxmately 15 sheets, in each sheet there's 4-7
colums with data . How can I choose sheet 5 for example as the data source?
When I use filtering (or if I just mark the sheet I wanna use) in any of
those sheets, it only merge with sheet number 1, and I want it to merge with
sheet number 5. What am I doing wrong? And how can I merge using several
sheets as data source?
I'm using Office 2000.

Any help would be appriciated!

Regards, Tina

 

[Peter Jamieson]

To use any sheet other than the first, you have to use the ODBC method to
connect to your data source (check the Select method box in the Open Data
Source and you will see an additional dialog box where you can select Excel
via ODBC). As for the filtering, you may find that you need to look at the
MS Query option, which also uses ODBC.

You'll probably also find that you need to
a. use formatting switches in Word [ MERGEFIELD } fields to format numbers
and dates, which may not appear as they do in Excel
b. close your spreadsheet before using it as a data source.

An alternative might be to re-sequence the sheets in Excel before each
merge.

There is a way to use ODBC to get all the data for all five sheets at once
(basically, you can use the SQL option in MS Query to create a UNION query),
but it could be simpler to create a sheet 6 or another worksheet, grab all
the data off the other sheets (I'm not an Excel expert so I don't know the
best way to do this), and use that as your data source.

 

[Tina-Jeanette Thorheim]

Thanks alot for your help, Peter!
Unfortunately, another problem occured. When I use MS Query to use filtered
database in Excel, the Zip codes changes to decimals (eg. 0030 Oslo, appear
in Word like this: 30.0, or eg. 5007 Bergen, appear in Word like this:
5007.0) Do you have any idea? I've formatted the colum in Excel as
zip-code, but that does'nt help.

Regards, Tina

To use any sheet other than the first, you have to use the ODBC method to
connect to your data source (check the Select method box in the Open Data
Source and you will see an additional dialog box where you can select Excel
via ODBC). As for the filtering, you may find that you need to look at the
MS Query option, which also uses ODBC.

You'll probably also find that you need to
a. use formatting switches in Word [ MERGEFIELD } fields to format numbers
and dates, which may not appear as they do in Excel
b. close your spreadsheet before using it as a data source.

An alternative might be to re-sequence the sheets in Excel before each
merge.

There is a way to use ODBC to get all the data for all five sheets at once
(basically, you can use the SQL option in MS Query to create a UNION query),
but it could be simpler to create a sheet 6 or another worksheet, grab all
the data off the other sheets (I'm not an Excel expert so I don't know the
best way to do this), and use that as your data source.

--
Peter Jamieson
MS Word MVP

Hi,
I'm trying to mail merge labels with Excel sheets as data source. My data
source in Excel is large, aproxmately 15 sheets, in each sheet there's 4-7
colums with data . How can I choose sheet 5 for example as the data source?
When I use filtering (or if I just mark the sheet I wanna use) in any of
those sheets, it only merge with sheet number 1, and I want it to merge with
sheet number 5. What am I doing wrong? And how can I merge using several
sheets as data source?
I'm using Office 2000.

Any help would be appriciated!

Regards, Tina

 

[Peter Jamieson]

Sorry, can't check right now but try either:
a. altering the SQL in your MS Query code to use e.g.

cstr(Zipcode) AS 'zip'

instead of

Zipcode

b. does it make any difference if you format your Excel column as /text/?

NB can we assume that the postcodes in a separate column in your sheet?

(FWIW if you are always using the "European"

0123 Cityname

type of postcode I can't really see the advantage of using Zip type columns
in Excel.)

--
Peter Jamieson
MS Word MVP

Thanks alot for your help, Peter!
Unfortunately, another problem occured. When I use MS Query to use filtered
database in Excel, the Zip codes changes to decimals (eg. 0030 Oslo, appear
in Word like this: 30.0, or eg. 5007 Bergen, appear in Word like this:
5007.0) Do you have any idea? I've formatted the colum in Excel as
zip-code, but that does'nt help.

Regards, Tina

To use any sheet other than the first, you have to use the ODBC method to
connect to your data source (check the Select method box in the Open Data
Source and you will see an additional dialog box where you can select Excel
via ODBC). As for the filtering, you may find that you need to look at the
MS Query option, which also uses ODBC.

You'll probably also find that you need to
a. use formatting switches in Word [ MERGEFIELD } fields to format numbers
and dates, which may not appear as they do in Excel
b. close your spreadsheet before using it as a data source.

An alternative might be to re-sequence the sheets in Excel before each
merge.

There is a way to use ODBC to get all the data for all five sheets at once
(basically, you can use the SQL option in MS Query to create a UNION query),
but it could be simpler to create a sheet 6 or another worksheet, grab all
the data off the other sheets (I'm not an Excel expert so I don't know the
best way to do this), and use that as your data source.

--
Peter Jamieson
MS Word MVP

Hi,
I'm trying to mail merge labels with Excel sheets as data source. My data
source in Excel is large, aproxmately 15 sheets, in each sheet there's 4-7
colums with data . How can I choose sheet 5 for example as the data source?
When I use filtering (or if I just mark the sheet I wanna use) in any of
those sheets, it only merge with sheet number 1, and I want it to

merge
with

sheet number 5. What am I doing wrong? And how can I merge using several
sheets as data source?
I'm using Office 2000.

Any help would be appriciated!

Regards, Tina