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