"Word was unable to open the Excel data source" Asp.net Mailmerge

[Shalini]

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

 

[Doug Robbins]

Looking at your code, it appears that the initially, MainDocumentType is
being set to wdNotAMergeDocument and that is probably the reason that you
are getting the error message.

You do later change the MainDocumentType, but I think you will have to do
that before you attempt to attach the data source.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Shalini]

Thanks for your prompt reply.

But hard luck, even after commenting that statement, i am still getting the
same error.

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 88

Please do the needful.

 

[Matt]

Hi Shanli,

I needed to do a merge in a windows forms app and used your code. I made a
change to the excel datasource and that was about it... It seems to work for
me with office 2003. If you have any other enhancements, please post them.

Regards,

Matt

Dim wrdApp As Word.Application
Dim wrdDoc As Word._Document
Dim wrdDoc1 As Word.MailMergeDataSource
Dim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge
Dim wrdMergeFields As Word.MailMergeFields
Dim fileWORDName, fileEXCELName As String

Try
wrdApp = New Word.Application

fileWORDName = "C:\merge.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.wdFormLetters

wrdApp.Visible = False
wrdDoc.ActiveWindow.Activate()
wrdDoc.ActiveWindow.Visible = True

wrdMailMerge.OpenDataSource(Name:="c:\TestMerge.xls",
SQLStatement:="SELECT * FROM `Results$`") 'Don't forget the Sheetname

' Perform mail merge.
wrdMailMerge.MainDocumentType =
Word.WdMailMergeMainDocType.wdFormLetters
wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument
wrdMailMerge.SuppressBlankLines = True
wrdMailMerge.DataSource.FirstRecord =
Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
wrdMailMerge.DataSource.LastRecord =
Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
wrdMailMerge.Execute(True)

' Close the original form document.
wrdDoc.Saved = True
wrdDoc.Close(False)

Catch ex As Exception
MessageBox.Show(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
wrdMailMerge = Nothing
wrdDoc = Nothing
wrdApp = Nothing
End Try