My last response was not a rant at you, I hope you did not take it that
Not at all.
For my part, I've worked in support (and other areas) for over 25 years and,
although I'm probably somewhat jaded now, I do still try to see things from
the point of view of ordinary users who just want to get their stuff done.
Unfortunately,
a. there is a huge gap between something that "just works" and "you just
have to do this one thing to make it work," and in most cases, it isn't just
one thing. Since I do have a "techie" background, it took quite a while to
learn that most people will perceive a sequence of (say) 3 or more steps as
complicated, however simple the steps.
b. most people could live with a /surefire/ workaround for the kind of
problem you mention. But they still have to do it, or install it, and so on,
and that's complicated in itself. Worse, trying to produce gerneal-purpose
workarounds that are guaranteed to work is virtually impossible.
You went through the various merge options and said that there is a
problem with every one of them. It does seem that it is about time they
overhauled this part of Word completely - chuck out these various methods
that don't work properly and install one that does.
Broadly speaking I agree. At one time I tried rather harder to get this
point across than I do today. I think some of the problems facing software
designers are probably harder to fix than people usually realise, but there
are many areas in which things can be solved but in which they seem to have
been getting worse rather than better. Of late, the vast majority of
problems seem to be related to "security fixes".
All I know is that this isn't going to happen any time soon.
BTW - What problems are there with DDE that would cause MS to discourage
its use?
Personally I've never had much problem with using DDE, but I read that it is
regarded as insecure. I can't tell you /why/, or whether it is the concept
of DDE that's inevitably going to lead to insecurity, or some feature of the
design, or simply the implementation. The thing is that MS started trying to
replace DDE by something "better" a very long time ago - originally it was a
thing called OLE (1), which was built on top of DDE, but that was replaced
by OLE2, which wasn't, and from that point on I think DDE has been "in
maintenance".
However, a significant /shortcoming/ for some types of user is that to use
DDE to connect to Access or Excel data, you have to have Access/Excel on
your system. With the Excel converter, ODBC, and OLEDB, you don't. For
anyone needing to automate Word on behalf of other users, the fact that Word
has to start a second, visible program is a significant complication.
Because significant DDE development stopped long ago, there are other
problems, e.g. you can't see Unicode format data (important for some), and
you only get to see one worksheet in an Excel workbook.
Anyway, glad you got your problem sorted.
Peter Jamieson
Glossary:
DDE Dynamic Data Exchange (for communicating between programs)
OLE Object Linking and Embedding (for what it says)
ODBC Open Data Base Connectivity (or something like that - this has
nothing to do with OLE)
OLEDB OLE Data Base - a set of standards built on top of OLE2 for
accessing data.
PBUs - the Poor B***** Users who have to deal with it all (one of our
favourite TLAs where I used to work, and one that The Industry could
usefully thinkk about a bit more)
TLA - Three Letter Abbreviation. Or TriLiteral Abbreviation
PT said:
Hello Peter,
My last response was not a rant at you, I hope you did not take it that
way - it was aimed at no one in particular, except perhaps Microsoft.
You went through the various merge options and said that there is a
problem with every one of them. It does seem that it is about time they
overhauled this part of Word completely - chuck out these various methods
that don't work properly and install one that does.
Not everybody can or wants to get involved in the workaround methods to
make them work, nor does everyone have an IT expert in the next room to
call on. It is easier to keep the old software that does work.
Thank you for your comments. They have made me think that there may be
something about the Excel file. It was actually from a friend who exported
it from Filemaker Pro on a Mac. It looks fine in Excel but your comments
made me wonder if there is something under the surface that Word did not
like.
I selected the whole thing (Ctrl-A) then copied and pasted it to a new
file that I saved with a new name and was able to get it to connect to
Word with the DDE option. The dates look the way I want them to which was
the problem I had.
BTW - What problems are there with DDE that would cause MS to discourage
its use?
Paul
Peter Jamieson said:
If your spreadsheet has fewer than 64 columns, the simplest way to solve
this is probably to copy/paste your data into a Word document and use
that as your data source. That may be a pain, but frankly it's probably
less of a pain than any of the other solutions. if you have more than 63
columns, you may be able to do the same but you will need to use Word
Paste|Special and select the "unformatted text" option rather than
pasting the data in as a table, and stuff such as multiline text in Excel
may cause problems.
FWIW, there are 4 ways you can connect to an Excel spreadsheet in Word
MailMerge:
a. DDE (the default in Word 2000, but its use has been discouraged by MS
for a long time and we are seeing more and more reports of problems)
b. ODBC (available in most versions of Word on Windows), which suffers
the problems you describe)
c. OLEDB (similar to ODBC but newer. Only available in Word 202/2003. The
default in Word 2002/2003). Suffers many of the same problems as ODBC and
has one problem of its own.
d. The Excel converter (no longer supported, but you may still be able to
download it from somewhere on Graham Mayor's site at
http://www.gmayor.com )
All of them suffer from one type of shortcoming or another.
DDE should work, although it has not really been supported for a long
time and increasing numbers of people are reporting problems, and the
alternatives (ODBC as you mention and OLEDB) suffer from the some or all
of the problems you mention.
On the DDE front,
a. is the sheet you are trying to use the only one in the workbook? Or
the first in the workbook? Or the one you last had open when you closed
the workbook?
b. what happens if you start with a completely new test spreadsheet,
created from scratch? Do you still experience the problems?
Try starting with
c. Excel closed
d. Excel open
e. Excel open and your spreadsheet open
You should also ensure that Excel|Tools|Options|General|Ignore other
applications is unchecked (it is unchecked by default)
If (d) works but not (c), make sure Excel isn't executing any macros when
it starts.
If (e) works but not (d), make sure your workbook has no startup macros.
Let's not get into ODBC/OLEDB unless we really have to... (but if you go
to Google Groups and search this newsgroup for
Peter Jamieson typeguessrows
you may find some info. you can use.
Wish it was easier...
Peter Jamieson
PT said:
I have a listing of books in an Excel spreadsheet. Everything works fine
in Word 2000 and Excel 2000 but not if I move to my newer computer with
Word 2003 and Excel 2003.
The master document is a Word table and it is designed to select only
some of the fields from the spreadsheet. If I select to use the DDE
connection to the spreadsheet Word does not find the data document - the
process of selecting the document just fails and goes round in circles.
If I select the Excel file via ODBC (whatever that is) I then get a
System Options box and have to select both "Tables" and "System Tables"
to see '[directoryname]#CSV$' selected which will then connect Word to
the data.
OK so far but the numbers that are merged are not right. One column
contains dates. Some are just years, others are like "Dec-82". The
merged document displays the years as "1986.0" and the others as a five
figure number- "Dec-82" is merged as "30286.0".
I have tried formatting the cells as text, that does not seem to change
anything, Excel still wants to see the string as a date. Entering the
date preceded by a ' character makes the entry in the Word merged
document not appear, I get a blank cell in the table.
I am not a programmer and don't want to get into complicated programming
tricks. I would just like the thing to work the way it used to.
Any help would be appreciated.
Paul
