[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

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