Word (2000) Mail Merge Using Excel as Data Source Doesn't Work Properly

[Mark]

I'm attempting to merge a worksheet from an Excel workbook
of several (worksheets), and the wrong data is being
returned.

The closest I can get to what I want is data from another
workbook (apparently of Microsoft's choosing) in the file.

Here's what I'm doing & the results:

1. I click on the mail merge button (the end result is the
same if I go through the wizard).

2. The 'Select Data Source' dialog box which appears.

3. I select the Excel file which contains the desired
worksheet.

4. The 'Data Link Properties' dialog box appears,
directing me to select/enter an ACCESS DATABASE name (the
harbinger of coming doom since I want to use Excel).

5. The file I'd selected in the 'Select Data Source'
dialog box is the default name entered, so I click 'OK'.

6. The 'ODBC Excel Driver Login Failed' dialog box appears
with the message "Could not decrypt file."

6.a. If I click 'OK', the 'Select Workbook' dialog box
appears again, throwing me into a vicious cycle of
selecting the file and receiving the "Could not decrypt
file." message again and again.

6.b. If I click 'Cancel', the 'Microsoft Excel' dialog box
appears, with a 'Named or cell range' field & list with
only one choice: "Entire Spreadsheet"

6.b.1. If I click 'OK', an ARBITRARILY selected worksheet -
not the desired one - is imported.

6.b.2. If I click 'Cancel', of course, I get nothing.

I can't be the only person one earth having this problem,
but I can't find the answer anywhere.

 

[Peter Jamieson]

I'm assuming you are really talking about Word 2002 or later, not 2000 as in
your message subject, since
a. in Word 2002 you have Select Data Source but in Word 2000 you have Open
Data Source
b. the connection method you describe is using OLEDB which is not supported
in Word 2000.

i.e., if you /are/ using Word 2000, it sounds to me as if something is
/really/ screwed up.

In Word 2002, you will see what you have described if you have protected the
Workbook using Tools|Protection|Protect Workbook. The simplest workaround to
the problem is to revert to the old DDE method of opening the sheet - check
Word Tools|Options|General|"Confirm conversions at open", go through the
Select Data Source process again, and select the Excel via DDE option when
prompted. I think this is the only way Word can get data from a protected
Excel workbook.

If that isn't the problem, perhaps there is some other problem with the
workbook (e.g. old version, corrupt, etc.)

The background to this problem is that if you do not check this option, Word
will try a number of connection methods in succession - I think it is OLEDB,
ODBC, then DDE.

Because of the protection, OLEDB fails in the first instance and displays
the Data Link dialog box so you can specify any further information
necessary to open the file. (However, this may not happen if the sheet is
already open in Excel, which I find slightly curious). The dialog box
mentions Access because there is no separate OLEDB provider for Excel -
OLEDB uses the Access/Jet provider, which in turn uses the "Excel IISAM" to
get the data. But unless you can work out how to specify the necessary
password (and I do not think it is possible), OLEDB will fail.

Word then tries ODBC. But ODBC will also fail because it does not know how
to read a protected workbook. So it also assumes you might need to provide
further info, and pops up its own dialog box. But this particular dialog box
can also be (very) misleading because it is not necessarily displaying the
details of the file you first thought of, and the options are usually set
initially in an unhelpful way. Also, if you try to select the correct file
using the drop-down list at the bottom right, typically you cannot see the
entire pathname, which can make selection difficult. But I think this is the
point where Microsoft is selecting a different workbook as you mention. If
instead you cancelled at this point, Word would probably go on to open the
workbook using DDE, which would probably succeed. I do not think there is
any way to specify the workbook password for ODBC either.

Quite /why/ OLEDB and ODBC cannot open protected workbooks I do not know -
it isn't as if you actually have to provide a password just to /open/ the
workbook in Excel. But I assume "protection" is implemented using encryption
and that the IISAM/driver/provider were never updated to include the code
that knew how to decrypt the .xls. Incidentally, the "Converter" connection
method cna't open protected workbooks either.