bgernon
asked on
Fill Word Template (.dot) fields from vb.net windows app datatable or dataset
I have a Word form letter template with the following fields:
«EmployerName»
«EmployerAddressLine1»
«EmployerAddressLine2»
«EmployerAddressLine3»
«EmployerCity», «EmployerStatePostalCode» «EmployerZipCode»
In a windows application the user should be able to click a button and create a merged document(s). The final document(s) will have the fields from a recordset, datatable, or datagridview. The data is retrieved through a SqlConnection query.
After combing the web, the attached code is as far as I've gotten. It looks like I have to build a tab delimited file first, then use that. I was hoping to use the DataTable or DataGridView to fill the fields.
Either way, I could use some help on getting the data from my query into the merge fields in the Word template.
Thx
«EmployerName»
«EmployerAddressLine1»
«EmployerAddressLine2»
«EmployerAddressLine3»
«EmployerCity», «EmployerStatePostalCode» «EmployerZipCode»
In a windows application the user should be able to click a button and create a merged document(s). The final document(s) will have the fields from a recordset, datatable, or datagridview. The data is retrieved through a SqlConnection query.
After combing the web, the attached code is as far as I've gotten. It looks like I have to build a tab delimited file first, then use that. I was hoping to use the DataTable or DataGridView to fill the fields.
Either way, I could use some help on getting the data from my query into the merge fields in the Word template.
Thx
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core
Imports Microsoft.Office
Private Sub btnWord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWord.Click
Dim wdApp As New Word.Application()
Dim wdDoc As New Word.Document
Dim strLabelName ="path to .dot template"
wdApp.Visible = True
wdApp = CreateObject("Word.Application")
wdDoc = wdApp.Documents.Open(CType(strLabelName, System.Object), False, True, _
False, , , True, , , _
Word.WdOpenFormat.wdOpenFormatDocument, , True)
wdDoc = wdApp.Documents.Add
...Code to fill .dot fields from DataTable or Recordset here
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well...I've got it working up to the point where I build my source document. When I am done with the merge,I need to remove the source document, WordData.doc in your example, so that the next user will not get an error message when they do a SaveAs. What code would delete the file?
ASKER
melmers, I've got it working. Can you tell me how to suppress a field if there is no data so there are no blanks in the address? Sometimes there is not an EmployerAddressLine2 or EmployerAddressLine3, so there is a big gap in the address between the last address line printed and the City, State, zipcode line.
I figured out how tho delete my source document. System.IO.File.Delete("C:\ ...WordTab le.doc")
I figured out how tho delete my source document. System.IO.File.Delete("C:\
if you have in your adress line blanks, then you must increment the column position in the datatable.
for example:
(Record with all data)
Name
AddressLine1
AddressLine2
AddressLine3
City
State
will result in a table with
Name|AddressLine1|AddressL ine2|Addre ssLine3|Ci ty|State
(Record with limitied data)
Name
AddressLine1
<<empty>>
<<empty>>
City
State
must result in a table with
Name|AddressLine1|City|Sta te|<<empty >>|<<empty >>
for example:
(Record with all data)
Name
AddressLine1
AddressLine2
AddressLine3
City
State
will result in a table with
Name|AddressLine1|AddressL
(Record with limitied data)
Name
AddressLine1
<<empty>>
<<empty>>
City
State
must result in a table with
Name|AddressLine1|City|Sta
ASKER
I'm not following. Could you please elaborate some.
i have tested this with the code i provided.
in my word file there is the field named "zusatz" , wich is populated every 10 rows.
The code runs if you create the folder c:\Test and you copy the MMTest.doc into this folder.
the field zusatz would only be displayed if there is anydata if not the whole row will not
be displayed. but if there are any characters after the field zusatz then the row will be
stay there so you must have a look at this in your template that you have no characters on the same row.
in my word file there is the field named "zusatz" , wich is populated every 10 rows.
The code runs if you create the folder c:\Test and you copy the MMTest.doc into this folder.
the field zusatz would only be displayed if there is anydata if not the whole row will not
be displayed. but if there are any characters after the field zusatz then the row will be
stay there so you must have a look at this in your template that you have no characters on the same row.
Dim objSqlDataSet As New DataSet
Dim myDataTable As New DataTable("mydata")
myDataTable.Columns.Add("name", GetType(String))
myDataTable.Columns.Add("phone", GetType(String))
myDataTable.Columns.Add("zusatz", GetType(String))
Dim myArray(2) As Object
For i As Integer = 0 To 100
myArray(0) = "Name" & i
myArray(1) = "Phone: " & i
If i = 10 Or i = 20 Or i = 30 Or i = 40 Or i = 50 Or i = 60 Or i = 70 Then
myArray(2) = "zusatz: " & i
End If
myDataTable.Rows.Add(myArray)
Next
objSqlDataSet.Tables.Add(myDataTable)
Dim myWord As New Microsoft.Office.Interop.Word.Application
Dim myDataDoc As Microsoft.Office.Interop.Word.DocumentClass = myWord.Documents.Add
Dim myTable As Microsoft.Office.Interop.Word.Table = myDataDoc.Tables.Add(myWord.ActiveDocument.Range(0, 0), 1, 3)
myTable.Rows(1).Cells(1).Range.Text = "name"
myTable.Rows(1).Cells(2).Range.Text = "phone"
myTable.Rows(1).Cells(3).Range.Text = "zusatz"
For Each myRow As DataRow In objSqlDataSet.Tables("mydata").Rows
Dim wRow As Microsoft.Office.Interop.Word.Row = myTable.Rows.Add()
wRow.Cells(1).Range.Text = myRow.Item("name")
wRow.Cells(2).Range.Text = myRow.Item("phone")
If myRow.IsNull("zusatz") = False Then
wRow.Cells(3).Range.Text = myRow.Item("zusatz")
End If
Next
myDataDoc.SaveAs("C:\Test\WordData.doc")
myDataDoc.Close()
Dim myMailMergeDoc As Document = myWord.Documents.Open("C:\Test\MMTest.doc")
myMailMergeDoc.MailMerge.OpenDataSource(Name:="C:\Test\WordData.doc")
myMailMergeDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument
myMailMergeDoc.MailMerge.Execute()
myWord.ActiveDocument.SaveAs("c:\Test\MMResult.doc")
myWord.Visible = True
myMailMergeDoc.Close()
myWord.Quit()
MsgBox("Complete")
MMTest.doc
ASKER
The address fields are NOT NULL in the database table. Looks like you are checking for a null vulue with isNull. Thanks anyway. I am going with what I have now. I think the fix is actually in the template itself. I need to move on to other problems, like printing out my datagridview.
ASKER
Dim myTable As Microsoft.Office.Interop.W
Is this building a document with a table that will be used to merge with the template document? What does .Range(0,0),1,2) do? If I have 7 fields, should the line read .Range(0,0),1,7)?