Fill Word Template (.dot) fields from windows app datatable or dataset

I have a Word form letter template with the following fields:

«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.

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

Open in new window

Who is Participating?
bgernonAuthor Commented:
I'm studying the example.  Can you tell me what this line does?

Dim myTable As Microsoft.Office.Interop.Word.Table = myDataDoc.Tables.Add(myWord.ActiveDocument.Range(0, 0), 1, 2)

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)?
bgernonAuthor Commented:
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?

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

bgernonAuthor Commented:
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:\...WordTable.doc")
if you have in your adress line blanks, then you must increment the column position in the datatable.

for example:
(Record with all data)

will result in a table with

(Record with limitied data)
must result in a table with

bgernonAuthor Commented:
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.
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
        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
        Dim myMailMergeDoc As Document = myWord.Documents.Open("C:\Test\MMTest.doc")
        myMailMergeDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument
        myWord.Visible = True

Open in new window

bgernonAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.