R
rc
I am trying to work with the word object model via .Net and have managed with
some success in setting a mail merge using SQL Sever successfully, I am now
receiving unable to open data source, although when I use word directly using
the ODC file, and view I have created I have success, secondly whilst
exploring the functionality I have tried use calls to stored procedures in
the SQL and whilst the connection seems to be established the only merge
fields that get populated are the number fields, if anyone can shed some
light on the limitations correct method of doing this it would be much
appreciated, in particular I would prefer not to use an ODC file but it
appears to be the only option and would really like to call a stored proc.
any eamples would be great.
In anticipation thanks for your help
r.Report.SQL= "SELECT * FROM ""FullfillTemp"""
it dosnt make much difference if I add some where clause allthough I would
like to change to "exec storedProc p1,p2 etc". and why do you need so many
quotes?
r.Report.DBodcConnection= a valid ODC file
r.Report.DSNConnection = a valid connection string
Dim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge
Dim wrdMergeFields As Word.MailMergeFields
Try
' Create an instance of Word and make it visible.
wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
wrdApp.ActivePrinter = printerName
' Add a new document.
wrdDoc = wrdApp.Documents.Add(r.Report.WordDocTemplate)
wrdDoc.Select()
wrdMailMerge = wrdDoc.MailMerge()
'' Create MailMerge Data file.
'CreateMailMergeDataFileDocument()
Dim mmd As Word.MailMergeDataSource
mmd = wrdMailMerge.DataSource
wrdMailMerge.OpenDataSource(Name:=r.Report.DBodcConnection,
Connection:=r.Report.DSNConnection, SQLStatement:=r.Report.SQL)
With wrdMailMerge.DataSource
..FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
..LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
End With
' Perform mail merge.
If Preview = False Then
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToPrinter
Else
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
wrdApp.Visible = True
End If
wrdMailMerge.SuppressBlankLines = True
wrdMailMerge.Execute(True)
' Close the original form document.
wrdDoc.Saved = True
'' Clean up temp file.
'System.IO.File.Delete(cMergeDataDoc)
Catch ex As Exception
Me.Cursor = Cursors.Default
MessageBox.Show(ex.Message)
Finally
Try
'Close the main document
wrdDoc.Close(False)
Catch ex As Exception
'dosnt matter if this falls through
End Try
' Release References.
wrdSelection = Nothing
wrdMailMerge = Nothing
wrdMergeFields = Nothing
wrdDoc = Nothing
wrdApp = Nothing
Me.Cursor = Cursors.Default
End Try
some success in setting a mail merge using SQL Sever successfully, I am now
receiving unable to open data source, although when I use word directly using
the ODC file, and view I have created I have success, secondly whilst
exploring the functionality I have tried use calls to stored procedures in
the SQL and whilst the connection seems to be established the only merge
fields that get populated are the number fields, if anyone can shed some
light on the limitations correct method of doing this it would be much
appreciated, in particular I would prefer not to use an ODC file but it
appears to be the only option and would really like to call a stored proc.
any eamples would be great.
In anticipation thanks for your help
r.Report.SQL= "SELECT * FROM ""FullfillTemp"""
it dosnt make much difference if I add some where clause allthough I would
like to change to "exec storedProc p1,p2 etc". and why do you need so many
quotes?
r.Report.DBodcConnection= a valid ODC file
r.Report.DSNConnection = a valid connection string
Dim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge
Dim wrdMergeFields As Word.MailMergeFields
Try
' Create an instance of Word and make it visible.
wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
wrdApp.ActivePrinter = printerName
' Add a new document.
wrdDoc = wrdApp.Documents.Add(r.Report.WordDocTemplate)
wrdDoc.Select()
wrdMailMerge = wrdDoc.MailMerge()
'' Create MailMerge Data file.
'CreateMailMergeDataFileDocument()
Dim mmd As Word.MailMergeDataSource
mmd = wrdMailMerge.DataSource
wrdMailMerge.OpenDataSource(Name:=r.Report.DBodcConnection,
Connection:=r.Report.DSNConnection, SQLStatement:=r.Report.SQL)
With wrdMailMerge.DataSource
..FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
..LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
End With
' Perform mail merge.
If Preview = False Then
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToPrinter
Else
wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
wrdApp.Visible = True
End If
wrdMailMerge.SuppressBlankLines = True
wrdMailMerge.Execute(True)
' Close the original form document.
wrdDoc.Saved = True
'' Clean up temp file.
'System.IO.File.Delete(cMergeDataDoc)
Catch ex As Exception
Me.Cursor = Cursors.Default
MessageBox.Show(ex.Message)
Finally
Try
'Close the main document
wrdDoc.Close(False)
Catch ex As Exception
'dosnt matter if this falls through
End Try
' Release References.
wrdSelection = Nothing
wrdMailMerge = Nothing
wrdMergeFields = Nothing
wrdDoc = Nothing
wrdApp = Nothing
Me.Cursor = Cursors.Default
End Try