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

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

ASKER CERTIFIED SOLUTION

melmers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bgernon

ASKER

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)?

bgernon

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?

bgernon

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:\...WordTable.doc")

melmers

bgernon

ASKER

I'm not following. Could you please elaborate some.

melmers

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
 
            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")

Open in new window

MMTest.doc

bgernon

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.