Re: "Word was unable to open the Excel data source" Asp.net Mailme

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Are you using Word 2002 or 2003? If so,
a. does ASP.NET insist on having a full set of parameters in each method
call? If so you will need an extra parameter at the end:

Subtype=wdMergeSubtypeWord2000
b.I get the impression from the error message that the missing Subtype is
not causing a problem. If you do not need to use ODBC, I would try
simplifying the OpenDataSource so that it uses the default connection method
(OLEDB):

wrdMailMerge.OpenDataSource(Name:= _
fileEXCELName, _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=Word.WdOpenFormat.wdOpenFormatAuto, _
Connection:= "", _
SQLStatement:="SELECT * FROM 'Data'", _
SQLStatement1="")
c. With OLEDB, if you need the Subtype parameter, use
Subtype:=wdMergeSubtypeAccess
(even though it is Excel, not Access)

If that does not work, or you are using Word 2000, I do not know what is
going wrong. I would ensure that
a. making the same connection using the same code in Word VBA works - I
think it will be much easier to discover the source of the problem using VBA
b. the account that is running the ASP.NET process has the necessary
permissions to open the Excel file (in the case of ODBC, you may also need
to check that the account has access to the ODBC DSN. For example, if you
created a /user/ DSN under your account, that DSN will not be available to
other users - you would need a /system/ DSN, or a file DSN. With a file DSN
you would need to change some other stuff too.

Peter Jamieson


"Shalini" <Shalini@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1135AD6-B70A-4244-AA05-026A1C4F783B@xxxxxxxxxxxxxxxx
> Hi Peter,
>
> I tried to incorporate all the changes as recommended by you, and then
> also
> it is throwing an exception:
>
> ''System.Runtime.InteropServices.COMException (0x800A1722): Word was
> unable
> to open the data source. at Word.MailMerge.OpenDataSource(String Name,
> Object& Format, Object& ConfirmConversions, Object& ReadOnly, Object&
> LinkToSource, Object& AddToRecentFiles, Object& PasswordDocument, Object&
> PasswordTemplate, Object& Revert, Object& WritePasswordDocument, Object&
> WritePasswordTemplate, Object& Connection, Object& SQLStatement, Object&
> SQLStatement1) at FPA.JobIntegration.btnMailMerge_Click(Object sender,
> EventArgs e) in C:\Inetpub\wwwroot\FPA\JobIntegration.aspx.vb:line 90
>
> Also, the word file specified in the code is a simple word document with
> only fields' embedded as a normal text.
>
> waiting for your reply soon
>
> Regards,
> Shalini
>
>
> "Peter Jamieson" wrote:
>
>> You do not say what is going wrong and at what point it is happening,
>> but...
>>
>> > Dim fileWORDName, fileEXCELName As Object
>>
>> I do not know which is required or better in ASP.NET, but can these be
>> "As
>> String". Does it make any difference if they are?
>>
>> > wrdDoc = wrdApp.Documents.Open(FileName:=fileWORDName,
>>
>> If this document is already a Mail Merge Main Document, Word will look
>> for
>> its data source when OpenFileName executes. If it cannot find the data
>> source, Word will try to display a dialog box, so will probably hang in
>> your
>> situation. You cannot prevent this, so the only good solution is to be
>> certain that the document is not attached to a data source before your
>> ASP.NET routine tries to open it.
>>
>> > wrdApp.ChangeFileOpenDirectory("C:\Inetpub\wwwroot\FPA\Archive\")
>> > fileEXCELName = "FullfillmentReport1.xls"
>>
>> > wrdMailMerge.OpenDataSource(Name:= _
>> > fileEXCELName, _
>>
>> This /may/ work, but I would try
>>
>> fileEXCELName = "C:\Inetpub\wwwroot\FPA\Archive\FullfillmentReport1.xls"
>>
>> > Connection:= _
>> > "DSN=Excel
>> > Files;DBQ=fileEXCELName;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
>> > _
>>
>> The DBQ must be the name of the file. here, you are using a piece of text
>> "fileEXCELName". Try something like
>>
>> Connection:= _
>> "DSN=Excel Files;DBQ=" & fileEXCELName &
>> ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
>>
>> I suspect the fileEXCELName will have to be the full path name of the
>> file
>> (as above) for this to work.
>>
>> > SQLStatement:="SELECT * FROM 'Data'"
>>
>> This may be OK, but
>> a. it clearly requires that your Excel workbook has a named range called
>> Data
>> b. you /may/ need to use a different kind of single quotes around Data,
>> i.e. single back quotes:
>>
>> SQLStatement:="SELECT * FROM `Data`"
>>
>> Try those changes first. and let's see...
>>
>> Peter Jamieson
>>
>> From: "Shalini" <Shalini @discussions.microsoft.com>
>> Subject: "Word was unable to open the Excel data source" Asp.net
>> Mailmerge
>> Date: 17 June 2005 11:14
>>
>> Hi,
>>
>> I am trying to perform word mail merge operation via ASP.Net using Excel
>> as
>> data source.
>>
>> Code is attached below:
>> Dim wrdApp As Word.Application
>> Dim wrdDoc As Word._Document
>> Dim wrdMailMerge As Word.MailMerge
>> Dim fileWORDName, fileEXCELName As Object
>>
>> Try
>> wrdApp = New Word.Application
>>
>>
>> wrdApp.ChangeFileOpenDirectory("C:\Inetpub\wwwroot\FPA\Templates\")
>> fileWORDName = "ThankYou-Shalini.doc"
>>
>> wrdDoc = wrdApp.Documents.Open(FileName:=fileWORDName,
>> ConfirmConversions:=False, ReadOnly _
>> :=False, AddToRecentFiles:=False, PasswordDocument:="",
>> PasswordTemplate _
>> :="", Revert:=False, WritePasswordDocument:="",
>> WritePasswordTemplate:="" _
>> , Format:=Word.WdOpenFormat.wdOpenFormatAuto)
>>
>> wrdMailMerge = wrdDoc.MailMerge
>> wrdMailMerge.MainDocumentType =
>> Word.WdMailMergeMainDocType.wdNotAMergeDocument
>>
>> wrdApp.Visible = True
>> wrdDoc.ActiveWindow.Activate()
>> wrdDoc.ActiveWindow.Visible = True
>>
>>
>> wrdApp.ChangeFileOpenDirectory("C:\Inetpub\wwwroot\FPA\Archive\")
>> fileEXCELName = "FullfillmentReport1.xls"
>>
>> wrdMailMerge.OpenDataSource(Name:= _
>> fileEXCELName, _
>> ConfirmConversions:=False, ReadOnly:=False,
>> LinkToSource:=True, _
>> AddToRecentFiles:=False, PasswordDocument:="",
>> PasswordTemplate:="", _
>> WritePasswordDocument:="", WritePasswordTemplate:="",
>> Revert:=False, _
>> Format:=Word.WdOpenFormat.wdOpenFormatAuto, Connection:=
>> _
>> "DSN=Excel
>> Files;DBQ=fileEXCELName;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
>> , SQLStatement:="SELECT * FROM 'Data'",
>> SQLStatement1:="")
>>
>>
>>
>> ' Perform mail merge.
>> wrdMailMerge.Destination =
>> Word.WdMailMergeDestination.wdSendToNewDocument
>> wrdMailMerge.MainDocumentType =
>> Word.WdMailMergeMainDocType.wdFormLetters
>>
>> wrdMailMerge.SuppressBlankLines = True
>>
>> wrdMailMerge.DataSource.FirstRecord =
>> Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
>> wrdMailMerge.DataSource.LastRecord =
>> Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
>>
>>
>> wrdMailMerge.Execute(False)
>>
>> ' Close the original form document.
>> wrdDoc.Saved = True
>> wrdDoc.Close(False)
>>
>> Catch ex As Exception
>> Response.Write(ex.ToString)
>> Dim AllWORDProcess() As System.Diagnostics.Process =
>> System.Diagnostics.Process.GetProcessesByName("WINWORD")
>> Dim WordProcess As New System.Diagnostics.Process
>> For Each WordProcess In AllWORDProcess
>> WordProcess.Kill()
>> Next
>> WordProcess.Close()
>>
>> Dim AllEXCELProcess() As System.Diagnostics.Process =
>> System.Diagnostics.Process.GetProcessesByName("Excel")
>> Dim ExcelProcess As New System.Diagnostics.Process
>> For Each ExcelProcess In AllEXCELProcess
>> ExcelProcess.Kill()
>> Next
>> ExcelProcess.Close()
>> Finally
>> ' Release References.
>> wrdMailMerge = Nothing
>> wrdDoc = Nothing
>> wrdApp = Nothing
>> End Try
>>
>>
>> Please do the needful
>>
>>
>>
>>


.

Relevant Pages

  • Re: Word 2007 "Read-Only" error when changing source data
    ... If your data source is a .xls or .xslx (or whatever else it can be in Excel 2007) then Word will connect, by default, using OLE DB. ... I suspect it's where Word opens the data source file itself that it goes wrong. ... Peter Jamieson ... storing your data in a SQL Server or other server-based database. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2003 Mail Merge cannot open Excel 2003 document
    ... I redid it and saved as a csv file again and it worked! ... "Peter Jamieson" wrote: ... creating a very simple data source in Excel and see if that works OK. ...
    (microsoft.public.word.docmanagement)
  • Re: ODBC, Excel, SQL Server
    ... I think this might be caused by using an Excel data source that points to ... ODBC data source that you build using the ODBC Administrator. ... > and MSQuery...we have used ODBC DSN with no probs. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Unable to Open Data Source
    ... open because it didn't contain a valid database." ... "Peter Jamieson" wrote: ... the data you want to use from Excel, and/or copy/paste it into a Word table, ... >I ssend an email merge document successfully using an excel data source, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: mailmerge with txtfile
    ... "Peter Jamieson" wrote: ... > specify "Excel via DDE", when you open the data source file using VBA code, ... > the file as an Excel file. ... >>I followed the instructions you gave by creating a header file and I ...
    (microsoft.public.word.mailmerge.fields)