Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.

[ramudu]

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

 

[Peter Jamieson]

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]

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

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

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

 

[ramudu]

Hi Peter,

Thanks for your quick response.

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

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

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

 

[Peter Jamieson]

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

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

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

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

 

[ramudu]

Hi Peter,

That worked just great! Thanks a ton! You saved my vacation!

- Sri

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

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

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

 

[ramudu]

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

Hi Peter,

That worked just great! Thanks a ton! You saved my vacation!

- Sri

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

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

 

[Peter Jamieson]

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

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

Hi Peter,

That worked just great! Thanks a ton! You saved my vacation!

- Sri

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


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

 

[ramudu]

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

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

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

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


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

 

[Peter Jamieson]

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

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

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

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


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

 

[ramudu]

Hi Peter,

Yes, the DDE connection does work with smaller spreadsheets. In my case
it does up to 1103 rows.

Usually the spreadsheet has less amount than this, but in this case one
our clients has about 1220 rows.

The data in each cell is pretty small in itself. It mostly has numbers
(financial information), emails, names of companies etc.

- Sri

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

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

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



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


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