I'm guessing that DDE cannot handle the amount of data in the excel
spreadsheet.
You could be right, but that's something you could check experimentally.
Broadly speaking if the connection works when you do it manually, it should
work when you do it programmatically. If you can experiment with a smaller
Excel sheet then you should be able to establish whether the macro /ever/
connects.
So does it connect when you try manually? If not
a. is Excel definitely allowing DDE requests (there's an option in Excel
Tolls|Options that can prevent DDE from working
b. How many rows and columns are we talking about here, and do the fields
contain large amounts of info. (e.g. large texts) or small chunks of data -
numbers, mailing addresses, that sort of stuff?
Peter Jamieson
"ramudu" wrote in message
ups.com...
Hi Peter,
This gives the initial error "Word Could Not Re-Establish DDE
Connection to MS Excel to complete the task.".
I'm guessing that DDE cannot handle the amount of data in the excel
spreadsheet.
- Sri
Peter Jamieson wrote:
In Word 2003, the following seems to be enough for a workbook containing
a
single worksheet:
ActiveDocument.MailMerge.OpenDataSource _
Name:="the full path name of your workbook", _
Connection:="Entire Spreadsheet", _
SubType:=wdMergeSubTypeWord2000
You can specify a range name or a range in R1C1:RnCn format (but not in
A1:Zn format) in the connection parameter.
However, if the workbook is multisheet, things are tricky because as far
as
I know there's no way to specify a particular sheet in either the
Connection
or the SQLStatement parameter (which I've left out, above). What seems to
happen then is very confusing - Word appears to look at the sheet that is
currently displaying (or, if the workbook was closed when you called
OpenDataSource, the worksheet that was displaying when the workbook was
closed) and get the column and row count from there. However, it actually
gets the data from the first sheet in the book.
Peter Jamieson
"ramudu" wrote in message
oups.com...
Hi Peter,
One of the problems that I am facing using ODBC is that excel
spreadsheet does not open up automatically as it used to while using
DDE. This is a problem for some of my clients who are used to this.
You did mention that I can use DDE for the connection string. What
changes do I have to make to the connection string for this?
Thanks!
- Sri
ramudu wrote:
Hi Peter,
That worked just great! Thanks a ton! You saved my vacation! 
- Sri
Peter Jamieson wrote:
Sorry, got my wires crossed earlier.
For ODBC, you need
m_doc.MailMerge.OpenDataSource(Name:="", _
Connection:="DSN=Excel Files;DBQ=" & m_strDsName & ";", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
SubType:=WdMergeSubType.wdMergeSubTypeWord2000)
i.e.
a. I do not think you need the quotes around the pathname in the
connection
string (although they may not do any harm either) and you probably
do
not
need the other items such as DriverID and so on (but they won't do
any
harm
either).
b. the quotes around the sheet name have to be backquotes `` not
straight
quotes ''.
If you really need DDE, the problem is that you cannot connect to an
arbitrary sheet, whatever you specify. DDE will only open either the
first
sheet, or the sheet you had open when you last closed the workbook
(I'm
still looking at the details on that one).
Peter Jamieson
"ramudu" wrote in message
ups.com...
Hi Peter,
I tried this:
Dim sConnection As String
sConnection = "DSN=Excel Files;DBQ=""" &
m_strDsName &
""";DriverId=790;"
m_doc.MailMerge.OpenDataSource(Name:="", _
ConfirmConversions:=False, OpenExclusive:=False,
_
LinkToSource:=True, ReadOnly:=True, _
Format:=WdOpenFormat.wdOpenFormatAuto, _
Connection:=sConnection, _
SQLStatement:="SELECT * FROM 'Sheet1$'",
SQLStatement1:="", _
SubType:=WdMergeSubType.wdMergeSubTypeWord2000)
But this doesn't work either. It gives the error "Word was unable
to
open the data source."
Another thing - this might be trivial but notice that I have
quotes
around m_strDsName in the
sConnection string. If I don't give this, it immediately throws an
error saying it cannot connect to the data source.
- Sri
Peter Jamieson wrote:
If you have to use DDE, I suggest you try something along the
following
lines:
Dim sConnection As String
' leve the Sheet name out of the DBQ string. You may not need the
stuff
beyond DriverId either
sConnection = "DSN=Excel Files;DBQ=""" & _
m_strDsName &
";DriverId=790;MaxBufferSize
=2048;PageTimeout=5;"
' For a machine (user/system) DSN, leave Name:=""
m_doc.MailMerge.OpenDataSource(Name:="", _
SQLStatement:="SELECT * FROM 'Sheet1$'", _
SubType:=WdMergeSubType.wdMergeSubTypeWord2000)
There are other possibilities, but that's where I'd start.
Peter Jamieson
"ramudu" wrote in message
ups.com...
Hi,
We use mail merge in our application and for one our clients it
is
throwing the above error while generating the mail merge
document.
This
happens when I invoke the OpenDataSource() method.
The code for opening the spreadsheet looks something like this:
Dim o As Object = Type.Missing
m_doc.MailMerge.OpenDataSource(m_strDsName, _
o, o, o, o, o, o, o, o, o, o, _
"Data", o, o, o,
Word.WdMergeSubType.wdMergeSubTypeWord2000)
where m_strDsName is the physical location of the document.
In my case, it is able to get data up to a certain number of
rows
after
which it starts to throw an error. I looked at the Microsoft
support
website for the error,
http://support.microsoft.com/kb/196952
but this document doesn't help much in my case.
My Excel data source consists of one spreadsheet with one sheet
called
Sheet1 and the columns are dynamically generated.
In some posts in this and other groups I've seen that DDE is
not
reliable and using ODBC is encouraged. So I tried using an ODBC
connection and the code for the same looks like this.
Dim sConnection As String
sConnection = "DSN=Excel Files;DBQ=""" &
m_strDsName &
"![Sheet1]"";DriverId=790;MaxBufferSize =2048;PageTimeout=5;"
m_doc.MailMerge.OpenDataSource(Name:=m_strDsName, _
ConfirmConversions:=False,
OpenExclusive:=False, _
LinkToSource:=True, ReadOnly:=True, _
Format:=WdOpenFormat.wdOpenFormatAuto, _
Connection:=sConnection, _
SQLStatement:="SELECT * FROM 'Data",
SQLStatement1:="", _
SubType:=WdMergeSubType.wdMergeSubTypeWord2000)
This too throws an error 'Cannot establish connection to data
source'.
Help! Thanks!
- Sri