Excel Connection methods - overview
To get data from Excel, Windows Word can use one of four Connection Methods
that are (or were) provided "out-of-the-box", as follows
(some more changes may be needed for Word 2007):
| Short name |
Long name |
Description |
| DDE |
Dynamic Data Exchange |
DDE was the original standard Windows method for direct
communication between programs. DDE support is still provided as
standard in Windows, but little in Windows actually depends on
it any more. DDE was superseded by OLE (Object Linking and
Embedding) many years ago.
To use DDE to get data from Excel, Word tries to start Excel
if it is not already running. (So you have to have a copy of
Excel on the system that is doing the merge). Then it gets Excel
to open the workbook you want to use, if it is not already open.
Then it asks Excel to send the required data.
|
| Excel Converter |
Excel Converter |
A Standard Word/Office external "Text converter" that can
get text directly from an Excel .xls file. It works on systems
that do not have a copy of Excel.
However, the Excel converter is no longer supplied as part of
Office or as part of the Office Converter Pack on the Microsoft
web site, and in recent versions of Word the user will generally
face an additional security question if they try to use it.
|
| ODBC |
Open Database Connectivity |
ODBC is a general-purpose method of connecting to
"databases", retrieving and inserting data. Although
it is often thought of as a "Microsoft and Windows only" standard,
it isn't. Microsoft Office and Windows currently come with the
ODBC "driver" required to connect to Excel. In fact, ODBC
connections to Excel are made using the same ODBC driver that is
used to connect to Microsoft Access databases, i.e. the "Jet"
driver, which uses the "Jet Database engine" which knows how to
manipulate data in Access .mdb format databases. Jet can also
access data in other formats, either using ODBC drivers or Jet
components called IISAMs, one of which is used to access data in
Excel workbooks. As far as I know, only Microsoft has the
information necessary to create IISAMs.
In Office 2007 the Jet database engine has been enhanced and
renamed as "Ace" ("Access Connectivity Engine", which has its own ODBC driver (and
OLE DB
provider). However, at the moment there seems to be an error in
the ODBC driver that might prevent you from connecting to an
Excel Workbook via Ace.
|
OLE DB
(often written "OLEDB") |
OLE (Object Linking and Embedding) Database |
To a Word user making connections to data sources, OLE DB
works in a similar way to ODBC in that Excel is not required and
does not open. However, the OLE DB dialog boxes are different
from the ODBC ones. Microsoft
introduced OLE DB as a successor to ODBC. It can be seen as a
generalised version of ODBC rebuilt on top of Microsoft's OLE (which
later turned into "COM") programming model. OLE DB has "providers", and not
unnaturally, Microsoft supplies providers for several of its
products including SQL Server, Access/Jet/Ace, FoxPro and so on.
With ODBC, you access an Excel workbook using the Jet/Ace driver
and the Jet/Ace database engine's Excel IISAM. With OLE DB, you
access an Excel workbook using the Jet/Ace OLE DB provider and the
Jet/Ace database engine's Excel IISAM. |
If you are a developer, you may be wondering whether I have forgotten DAO, ADO and ADO.NET.
The short answer is that I haven't. Word doesn't use ADO.NET yet, and as
far as data source connections are concerned, it doesn't use DAO as far
as I know. It might use ADO to do its OLE DB stuff, but not in any that
is visible to the end user or programmer.
Comparison of Excel connection method features
| |
Excel connection method |
| Feature |
OLE DB |
ODBC |
Converter |
DDE |
Windows Word versions that the method
can be used with.
The sequence of versions was Word 1, Word 2, Word 6, Word 95,
Word 2000, Word 2002 (Word XP), Word 2003, Word 2007.
I haven't seen a Word 95 question for some time but there are
still Word 97 users "out there" |
Word 2002 and later |
Word 95 and later. There seems to
be a problem in Word 2007. |
Word 6 and later. Not sure about
2007 |
Word 6 and later |
| Excel files the method cannot be
used with. (there may be others). |
Excel Spreadsheet XML formats prior
to the Excel 2007 .xlsx forma |
Excel Spreadsheet XML formats prior
to the Excel 2007 .xlsx format |
Excel 2007 XML formats - .xlsx, .xslm etc. |
|
| Can the method be used with
encrypted workbooks (where a "Password to open" has been
provided? |
No, unless the workbook is already
open in Excel |
No, unless the workbook is already
open in Excel |
No |
Yes |
| Default method |
In 2002 and later |
|
|
2000 and earlier |
| "Status" (Mainly my
assumptions). Microsoft's main attention these days is arguably
given to ADO.NET which Word cannot yet use. |
The current standard. |
Not really supported |
Was withdrawn after Office 2002. |
Not really supported |
| Does not rely on Excel being
present or running (and notice that when Excel runs and opens
workbooks, it may run macros or display dialog boxes that block
further progress. Also, there is an Excel option to reject DDE
connections) |
Yes |
Yes |
Yes |
No |
| Can connect to any worksheet |
Yes |
Yes |
Yes |
No, just the "top" worksheet |
| Can connect to a named range |
Yes |
Yes |
Yes |
Only in the "top" worksheet |
| Can connect to a range specified
using A1:B3 format |
Only programmatically |
Only programmatically |
|
|
| Can connect to a range specified
using R1C1:RnCn format |
Only programmatically |
Only programmatically |
|
|
| Can connect without any dialog
boxes displaying |
yes |
yes |
No |
yes |
| Maximum column count |
|
|
|
|
| Can deliver Unicode characters -
i.e. Unicode characters outside the "ANSI" character set or the
"OEM" character set appropriate to non-English language versions
of Windows. This is a tricky area which I cannot claim to
understand in full! |
Yes |
No |
No |
No |
| Deals with special characters such
as double-quote (") characters correctly |
? |
? |
? |
? |
| The user can edit Excel data, in
Excel, while
the Word MailMerge Main document is open |
|
|
|
Yes |
| Word notices the changes the user
made while editing in Excel... |
? |
? |
? |
Changes to data in existing columns |
| Word will fail if the Excel sheet
is already open |
No, as far as I can tell |
No, as far as I can tell |
Pops up "Read-only" question |
No |
| Excel will fail if the Word sheet
is already open |
Pops up "File is open" dialog |
Pops up "File is open" dialog |
Pops up "File is open" dialog |
No |
|
