Merging with Excel

[clundey]

I've been trying to fix this for a user for a week, without any luck.
I'm using Office 2003 and want to do a mailmerge in Word with an Excel
datasource.
I click the "Open Data Source" button and select *.xls for type of file.
Browse out to the .xls data source. (First row=header, etc) and select it.

But instead of getting the "Select Table" requester with the list of
sheets and named ranges, I get the "Data Link Properties" requester
opened on the connection tab with the .xls document selected. If I
click OK, Word aborts.

This behavior is happening only on one W2K machine. Other W2K machines
are OK.

Thanks in advance for any help!

chris

 

[Peter Jamieson]

Is this the same problem just posted by "Anita Taylor"

Does the problem occur whatever .xls file you try to use as a data source?
even a new one?

Other than that, here's more or less what I said before and it is probably
just as relevant:

See
http://support.microsoft.com/kb/885832/en-us
You are prompted to locate the data source when you open the main document
of a mail merge in Word 2003

FWIW it normally works, but
a. what networking environment are you working in?
b. do other connection methods also fail (check Word
Tools|Options|General|"Confirm conversions at open", then try again)?

Despite the fact that you have reinstalled Office, it may be worth following
the instructions in

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

Peter Jamieson

 

[clundey]

Is this the same problem just posted by "Anita Taylor"

Does the problem occur whatever .xls file you try to use as a data source?
even a new one?

Other than that, here's more or less what I said before and it is probably
just as relevant:

See
http://support.microsoft.com/kb/885832/en-us
You are prompted to locate the data source when you open the main document
of a mail merge in Word 2003

FWIW it normally works, but
a. what networking environment are you working in?
b. do other connection methods also fail (check Word
Tools|Options|General|"Confirm conversions at open", then try again)?

Despite the fact that you have reinstalled Office, it may be worth following
the instructions in

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

Peter Jamieson

Hi Peter

It's similiar, but not with that error message. I activated the
"confirm data sources" as you suggested, and tried the connections
listed. It seems to be something with Excel via ODBC thats causing Word
to abort. 'Via DDE' and 'Via converter' are working as expected. CSV
and txt files are ok too.
The ODBC drivers are identically configured on both machines, so I am
looking at registries now.

The network is W2K3

Chris

 

[Peter Jamieson]

There are some clues here, but still not enough to point to a culprit or the
solution:
a. assuming you are using Word 2003 as you said, either you upgraded from
Word 97/2000 (or maybe 2002) which already had the Excel converter
installed, or you have installed it since. Although I can't think of a
reason why that would create a problem, is the converter pack installed on
both the machine that works and the one that doesn't? If not, it may
indicate that the configurations are more different than you are currently
assuming. (I guess if you're installing both machines from a common image,
that can't be the case tough)
b. OLEDB (the default method in Word 2003, and the only one which pops up
the "Datalink dialog box") and ODBC (which pops up a similar but different
dialog box) both use the Jet (Access) database engine to get their data. The
others do not (as far as I know). So it's probably either Jet or Jet Excel
IISAM registry entires you need to look at if that's where the problem lies.

A few thoughts:
a. OLEDB/ODBC won't, by default, open a password-protected Excel file
b. There may be a problem if Jet (not just Access) is set up to expect to
find a Workgroup Information (Access security) file. I'm speculating a bit
here though.
c. when you get tot eh ODBC dialog box, things can be a bit confusing
because you often cannot see the whole pathname of the .xls, and sometimes
the one you thought you selected is not the one selected in the dropdown.
The simplest way to test that there are no problems in that area is to
ensure the .xls has a short pathname. You generally have to check the
options button and check all the options to select the a sheet as well.

Peter Jamieson

 

[clundey]

There are some clues here, but still not enough to point to a culprit or the
solution:
a. assuming you are using Word 2003 as you said, either you upgraded from
Word 97/2000 (or maybe 2002) which already had the Excel converter
installed, or you have installed it since. Although I can't think of a
reason why that would create a problem, is the converter pack installed on
both the machine that works and the one that doesn't? If not, it may
indicate that the configurations are more different than you are currently
assuming. (I guess if you're installing both machines from a common image,
that can't be the case tough)
b. OLEDB (the default method in Word 2003, and the only one which pops up
the "Datalink dialog box") and ODBC (which pops up a similar but different
dialog box) both use the Jet (Access) database engine to get their data. The
others do not (as far as I know). So it's probably either Jet or Jet Excel
IISAM registry entires you need to look at if that's where the problem lies.

A few thoughts:
a. OLEDB/ODBC won't, by default, open a password-protected Excel file
b. There may be a problem if Jet (not just Access) is set up to expect to
find a Workgroup Information (Access security) file. I'm speculating a bit
here though.
c. when you get tot eh ODBC dialog box, things can be a bit confusing
because you often cannot see the whole pathname of the .xls, and sometimes
the one you thought you selected is not the one selected in the dropdown.
The simplest way to test that there are no problems in that area is to
ensure the .xls has a short pathname. You generally have to check the
options button and check all the options to select the a sheet as well.

Peter Jamieson

Peter

The Office 2003 install was an upgrade from 97. The problem was with
the key "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]".
The value for "win32old" was the same as the value for "win32".

Here is what a 'normal' machine has (This works as expected):

"win32"="C:\\Program Files\\Office2003\\OFFICE11\\msaexp30.dll"
"win32old"="C:\\WINNT\\System32\\msexcl40.dll"


Thanks for the registry advice!
Chris

 

[Peter Jamieson]

Chris,

Great piece of detection - I for one wouldn't have found it - thanks for the
feedback!

Peter Jamieson

There are some clues here, but still not enough to point to a culprit or
the solution:
a. assuming you are using Word 2003 as you said, either you upgraded
from Word 97/2000 (or maybe 2002) which already had the Excel converter
installed, or you have installed it since. Although I can't think of a
reason why that would create a problem, is the converter pack installed
on both the machine that works and the one that doesn't? If not, it may
indicate that the configurations are more different than you are
currently assuming. (I guess if you're installing both machines from a
common image, that can't be the case tough)
b. OLEDB (the default method in Word 2003, and the only one which pops
up the "Datalink dialog box") and ODBC (which pops up a similar but
different dialog box) both use the Jet (Access) database engine to get
their data. The others do not (as far as I know). So it's probably either
Jet or Jet Excel IISAM registry entires you need to look at if that's
where the problem lies.

A few thoughts:
a. OLEDB/ODBC won't, by default, open a password-protected Excel file
b. There may be a problem if Jet (not just Access) is set up to expect
to find a Workgroup Information (Access security) file. I'm speculating a
bit here though.
c. when you get tot eh ODBC dialog box, things can be a bit confusing
because you often cannot see the whole pathname of the .xls, and
sometimes the one you thought you selected is not the one selected in the
dropdown. The simplest way to test that there are no problems in that
area is to ensure the .xls has a short pathname. You generally have to
check the options button and check all the options to select the a sheet
as well.

Peter Jamieson

Peter

The Office 2003 install was an upgrade from 97. The problem was with the
key "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]". The
value for "win32old" was the same as the value for "win32".

Here is what a 'normal' machine has (This works as expected):

"win32"="C:\\Program Files\\Office2003\\OFFICE11\\msaexp30.dll"
"win32old"="C:\\WINNT\\System32\\msexcl40.dll"


Thanks for the registry advice!
Chris