Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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. 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 |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word Could Not Re-Establish DDE Connection to MS Excel to complete the task.
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 Peter Jamieson wrote: 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why dont MS just f**king re-write Word from scratch? Its dogsh*t | Microsoft Word Help | |||
How to put graphics on envelopes? | Microsoft Word Help | |||
I am having difficulty with deleting headers and footers | New Users | |||
Change paper size; Word changes to invalid margins | New Users | |||
WORD could not establish DDE connection to EXCEL to complete the t | Mailmerge |