[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7136
  • Last Modified:

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

0
bgernon
Asked:
bgernon
  • 5
  • 3
1 Solution
 
melmersCommented:
0
 
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)?
0
 
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?

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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")
0
 
melmersCommented:
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|AddressLine2|AddressLine3|City|State

(Record with limitied data)
Name
AddressLine1
<<empty>>
<<empty>>
City
State
must result in a table with
Name|AddressLine1|City|State|<<empty>>|<<empty>>

0
 
bgernonAuthor Commented:
I'm not following.  Could you please elaborate some.  
0
 
melmersCommented:
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
0
 
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now