By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,534 Members | 943 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,534 IT Pros & Developers. It's quick & easy.

Changing Mailmerge DataSource

P: n/a
I have existing word documents that we need to have the
data document reflect whether we're running in
development, test or production.

The data header is longer than 255 characters, so I cannot
put the structure in either createdatasource or
opendatasource so I have to build it on the fly.

The code I have written is as follows:

Try
sSql = "select * from ordersdatatable"
dsOrdersDataTable =
lungTransplantData.GetDataSet(sSql, "ordersdatatable")
Catch ex As Exception
MessageBox.Show("Failure to create
OrdersDataTable in merge document.")
End Try

Dim sHeader As String = ""
Dim x As Integer

For x = 0 To dsOrdersDataTable.Tables
("ordersdatatable").Columns.Count - 1
If x = 1 Then
sHeader = dsOrdersDataTable.Tables
("ordersdatatable").Columns(x).ColumnName
Else
sHeader = sHeader & "," &
dsOrdersDataTable.Tables("ordersdatatable").Column s
(x).ColumnName
End If
Next

With Wordobj.ActiveDocument
If .MailMerge.State =
Word.WdMailMergeState.wdMainAndDataSource Then
' We cannot use a connection
string here from the application due to compatibility with
Word.
.MailMerge.CreateDataSource
("OrdersDataTable.Doc", , , sHeader, , , , , True)

' Open the file to insert data.
wrdDataDoc = Wordobj.Documents.Open
("OrdersDataTable.doc")
wrdDataDoc.PageSetup.PaperSize =
Word.WdPaperSize.wdPaperLegal
wrdDataDoc.PageSetup.Orientation =
Word.WdOrientation.wdOrientLandscape
wrdDataDoc.Tables.Item(1).Rows.Add
()

Dim i As Integer
Dim OpenWordDoc As Word.Document

With wrdDataDoc.Tables.Item(1)
.AllowAutoFit = False
.Columns.Width = 15

For i = 1 To
dsOrdersDataTable.Tables("OrdersDataTable").Column s.Count
' Insert the data in the
specific cell.
If .Columns.Count < i - 1
Then
.Columns.Add()
End If
.Cell(1, i - 1).Range.Text
= dsOrdersDataTable.Tables("OrdersDataTable").Column s(i -
1).ColumnName
If Not IsDBNull
(dsOrdersDataTable.Tables("OrdersDataTable").Rows( 0).Item
(i - 1)) Then
.Cell(2, i -
1).Range.Text = dsOrdersDataTable.Tables
("OrdersDataTable").Rows(0).Item(i - 1)
Else
.Cell(2, i -
1).Range.Text = ""
End If
Next
End With

.MailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument
.MailMerge.Execute()
End If
End With
This all works pretty well with two exceptions:
1) wrdDataDoc winds up being the frontmost form....how do
I move it to the back or how can I close it without
affecting the main document.
2) Sometimes I receive a "Invalid Merge Field" dialog to
prompt for changes and then when I cancel, it works anyway.

Any suggestions will be much appreciated.

Thanks,

Bob Smith
Nov 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.