Making a catalogue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PT - 30 Sep 2006 00:44 GMT

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

Reply to this Message

Peter Jamieson - 30 Sep 2006 10:21 GMT

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

>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
[quoted text clipped - 24 lines]
> Any help would be appreciated.
> Paul

Reply to this Message

PT - 30 Sep 2006 11:34 GMT

Bloody amazing!!
Microsoft Word had an arrangement to do merges that worked every time with
no trouble and now the newer alternatives either don't work or require a
degree in rocket science to make them work.

Isn't there some SIMPLE answer or should I go back to 3x5 file cards? Or
maybe switch to a Mac and non-MS software.

My whole idea of using computers is to get the job done, not become enmeshed
in the technology, spend all day playing with it and lose sight of the task.

Paul

> 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
[quoted text clipped - 84 lines]
>> Any help would be appreciated.
>> Paul

Reply to this Message

Peter Jamieson - 30 Sep 2006 13:49 GMT

> My whole idea of using computers is to get the job done, not become
> enmeshed in the technology, spend all day playing with it and lose sight
> of the task.

So is mine, but unfortunately there's little I can do about it these days.

> Isn't there some SIMPLE answer

Is "just transfer all your Excel data to a Word table and maintain it there"
simple? Probably not, because you probably use your data for multiple
purposes and you need it in Excel for some of them.

If the copy/paste into Word works, I would consider that to be more "simple"
than changing to a completely new way of doing things just to solve this one
problem. Would I /like/ it? No, of course not.

Casting around for other software is one approach but then you probably want
to be sure that the replacement is going to do what you want, and only you
can really tell.

Peter Jamieson

> Bloody amazing!!
> Microsoft Word had an arrangement to do merges that worked every time with
[quoted text clipped - 99 lines]
>>> Any help would be appreciated.
>>> Paul

Reply to this Message

PT - 30 Sep 2006 14:13 GMT

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

Reply to this Message

Peter Jamieson - 30 Sep 2006 15:28 GMT

> My last response was not a rant at you, I hope you did not take it that
> way

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 :-)

> Hello Peter,
> My last response was not a rant at you, I hope you did not take it that
[quoted text clipped - 114 lines]
>>> Any help would be appreciated.
>>> Paul

Reply to this Message

Rate this thread: