Word-Excel 2000 Mail Merge Failure

[Jim]

I'm trying to run a mail merge using Word and Excel 2000.
My PC is a P3-1.13Ghz with 384 MB RAM running WinXP Pro
on a SBS2000 network.

The Excel spreadsheet contains 5 worksheets. I want to
be able to run the merge with the 5th sheet. The first
time I set it up, when opening my data source, I
checked "Select Method" and then selected the converter.
The merge ran just fine, and I continued fiddling about
(as you do) to perfect the results. After a while, I
closed everything down, saving the merge document along
the way.

When I returned to the document and opened it, I was
presented with the converter dialogue box (I also noticed
that Excel didn't start up automatically at this stage).
I selected the worksheet I wanted, but then I got the
message, "Word was unable to open the data source" and I
was presented with the dialogue box to either "find the
data source" or "remove merge information" (can't
remember the exact wording). However, when I tried to
reinitiate the link to the data source, I kept getting
the same "Word was unable to open the data source"
message.

So I deleted the Word merge document and decided to start
again with a new document. However, whenever I get to
the point of selecting the data source and then the
converter dialogue box opens and I select a sheet (any
sheet), I get the message, "Word was unable to open the
data source" and when I close the Mail Merge Helper, I
then get, "There is not enough memory or disk space to
convert this document."

I've tried rebooting and just in case there was some
issue with my network connections, I moved a copy of the
Excel spreadsheet to my hard drive, but still the same
thing happens. The DDE and ODBC options work, but I
can't select from a range of worksheets with these; I can
only use the first worksheet. I tried the merge again
and found that if I uncheck the "Select Method" option
when identifying the data source, everything works just
fine.

I'd really love this to work with the converter so that I
can select my preferred worksheet. Anybody have any
ideas? Thanks for your help.

Jim

 

[Peter Jamieson]

You should be able to use ODBC to select a particular worksheet, and
although it has its problems (blank numeric fields return 0, for one, and
long text fields may be truncated) IMO it is the best method if you need
multi-sheet access. But when you use ODBC and get to the dialog box to
select a sheet,
a. you need to make sure that the sheet you chose in the Open Data Source
is actually the one listed in the drop-down list of files in the dialog. It
is not usually easy to verify that unless the Excel pathname is short
enough.
b. You need to click the Options button and check all the boxes.