Solved

Word merge from vb.net

Posted on 2016-07-18
15
56 Views
Last Modified: 2016-07-22
Hello,
Need to merge the datatable values in  a word document with vb.net desktop application.
Have a format of letter.Need to insert fields from datatable into the word document fields.
Cheers
0
Comment
Question by:RIAS
  • 8
  • 5
  • 2
15 Comments
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41717677
To be clear, you are looking to do document generation off datatables created in VB?  Do you have an example Word Document template?  How are the variables listed in the document you want to replace?
0
 

Author Comment

by:RIAS
ID: 41717684
I think it will be called mailmerge
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41717691
I'm a bit confused now.  So the intent is to have a datatable that contains, for example:

name = John
last = Smith
city = Supertown

Open in new window


And your letter says:

Dear @name @last,

Thanks for living in @city.

-The Mayor

Open in new window


And it outputs for each datarow in datatable:

Dear John Smith,

Thanks for living in Supertown.

-The Mayor

Open in new window


Is that accurate?
0
 

Author Comment

by:RIAS
ID: 41717693
Perfect!
0
 
LVL 12

Accepted Solution

by:
Dustin Saunders earned 250 total points
ID: 41717723
Here is some sample code to get you going.

This will open your template file (oTemplate) and replace the variables with whatever you specify.  Just wrap this inside your "for each datarow in datatable" loop to set the variables you are replacing in the word document.  Then it saves to a variable file name (in the code here I manually made one) and closes/disposes of the word object.

        Dim oWord As Microsoft.Office.Interop.Word.Application
        Dim oDoc As Microsoft.Office.Interop.Word.Document
        Dim oTemplate As String = "C:\Users\DSaunders\Documents\_personal\EE\wordDocGeneration\letter.docx"

        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oDoc = oWord.Documents.Add

        oDoc = oWord.Documents.Open(oTemplate)

        oDoc.Content.Find.Execute(FindText:="[@name]", ReplaceWith:="John", Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll)
        oDoc.Content.Find.Execute(FindText:="[@last]", ReplaceWith:="Smith", Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll)
        oDoc.Content.Find.Execute(FindText:="[@city]", ReplaceWith:="Supertown", Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll)

        Dim outFile As String = "C:\Users\DSaunders\Documents\_personal\EE\wordDocGeneration\letter_" + "John" + "Smith" + ".docx"

        oDoc.SaveAs2(outFile)
        oDoc.Close()
        oDoc = Nothing
        oWord.Quit()
        oWord = Nothing

Open in new window


Sample word doc attached.
letter.docx
0
 

Author Comment

by:RIAS
ID: 41717735
Cheers mate.. Will try and keep you posted
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41717737
Don't forget to add a reference to the Microsoft Word Object Library! :)
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:RIAS
ID: 41717740
Yup
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 41719145
You could also perform the exact MailMerge functionality available in MS Word, from VB.Net as shown in this link:

https://support.microsoft.com/en-us/kb/301656
(How to automate Word to perform a mail merge from Visual Basic .NET)

Extract from above link:


Imports Microsoft.Office.Interop

Dim wrdApp As Word.Application
    Dim wrdDoc As Word._Document

    Private Sub InsertLines(ByVal LineNum As Integer)
        Dim iCount As Integer

        ' Insert "LineNum" blank lines.
        For iCount = 1 To LineNum
            wrdApp.Selection.TypeParagraph()
        Next iCount
    End Sub

    Private Sub FillRow(ByVal Doc As Word.Document, ByVal Row As Integer, _
    ByVal Text1 As String, ByVal Text2 As String, _
    ByVal Text3 As String, ByVal Text4 As String)

        With Doc.Tables.Item(1)
            ' Insert the data in the specific cell.
            .Cell(Row, 1).Range.InsertAfter(Text1)
            .Cell(Row, 2).Range.InsertAfter(Text2)
            .Cell(Row, 3).Range.InsertAfter(Text3)
            .Cell(Row, 4).Range.InsertAfter(Text4)
        End With
    End Sub

    Private Sub CreateMailMergeDataFile()
        Dim wrdDataDoc As Word._Document
        Dim iCount As Integer

        ' Create a data source at C:\DataDoc.doc containing the field data.
        wrdDoc.MailMerge.CreateDataSource(Name:="C:\DataDoc.doc", _
              HeaderRecord:="FirstName, LastName, Address, CityStateZip")
        ' Open the file to insert data.
        wrdDataDoc = wrdApp.Documents.Open("C:\DataDoc.doc")
        For iCount = 1 To 2
            wrdDataDoc.Tables.Item(1).Rows.Add()
        Next iCount
        ' Fill in the data.
        FillRow(wrdDataDoc, 2, "Steve", "DeBroux", _
              "4567 Main Street", "Buffalo, NY  98052")
        FillRow(wrdDataDoc, 3, "Jan", "Miksovsky", _
              "1234 5th Street", "Charlotte, NC  98765")
        FillRow(wrdDataDoc, 4, "Brian", "Valentine", _
              "12348 78th Street  Apt. 214", "Lubbock, TX  25874")
        ' Save and close the file.
        wrdDataDoc.Save()
        wrdDataDoc.Close(False)
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles Button1.Click
        Dim wrdSelection As Word.Selection
        Dim wrdMailMerge As Word.MailMerge
        Dim wrdMergeFields As Word.MailMergeFields

        Dim StrToAdd As String

        ' Create an instance of Word  and make it visible.
        wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = True

        ' Add a new document.

        wrdDoc = wrdApp.Documents.Add()
        wrdDoc.Select()

        wrdSelection = wrdApp.Selection()
        wrdMailMerge = wrdDoc.MailMerge()

        ' Create MailMerge Data file.
        CreateMailMergeDataFile()

        ' Create a string and insert it in the document.
        StrToAdd = "State University" & vbCr & _
                    "Electrical Engineering Department"
        wrdSelection.ParagraphFormat.Alignment = _
                    Word.WdParagraphAlignment.wdAlignParagraphCenter
        wrdSelection.TypeText(StrToAdd)

        InsertLines(4)

        ' Insert merge data.
        wrdSelection.ParagraphFormat.Alignment = _
                    Word.WdParagraphAlignment.wdAlignParagraphLeft
        wrdMergeFields = wrdMailMerge.Fields()
        wrdMergeFields.Add(wrdSelection.Range, "FirstName")
        wrdSelection.TypeText(" ")
        wrdMergeFields.Add(wrdSelection.Range, "LastName")
        wrdSelection.TypeParagraph()

        wrdMergeFields.Add(wrdSelection.Range, "Address")
        wrdSelection.TypeParagraph()
        wrdMergeFields.Add(wrdSelection.Range, "CityStateZip")

        InsertLines(2)

        ' Right justify the line and insert a date field
        ' with the current date.
        wrdSelection.ParagraphFormat.Alignment = _
               Word.WdParagraphAlignment.wdAlignParagraphRight
        wrdSelection.InsertDateTime( _
              DateTimeFormat:="dddd, MMMM dd, yyyy", _
              InsertAsField:=False)

        InsertLines(2)

        ' Justify the rest of the document.
        wrdSelection.ParagraphFormat.Alignment = _
               Word.WdParagraphAlignment.wdAlignParagraphJustify

        wrdSelection.TypeText("Dear ")
        wrdMergeFields.Add(wrdSelection.Range, "FirstName")
        wrdSelection.TypeText(",")
        InsertLines(2)

        ' Create a string and insert it into the document.
        StrToAdd = "Thank you for your recent request for next " & _
            "semester's class schedule for the Electrical " & _
            "Engineering Department. Enclosed with this " & _
            "letter is a booklet containing all the classes " & _
            "offered next semester at State University.  " & _
            "Several new classes will be offered in the " & _
            "Electrical Engineering Department next semester.  " & _
            "These classes are listed below."
        wrdSelection.TypeText(StrToAdd)

        InsertLines(2)

        ' Insert a new table with 9 rows and 4 columns.
        wrdDoc.Tables.Add(wrdSelection.Range, NumRows:=9, _
             NumColumns:=4)

        With wrdDoc.Tables.Item(1)
            ' Set the column widths.
            .Columns.Item(1).SetWidth(51, Word.WdRulerStyle.wdAdjustNone)
            .Columns.Item(2).SetWidth(170, Word.WdRulerStyle.wdAdjustNone)
            .Columns.Item(3).SetWidth(100, Word.WdRulerStyle.wdAdjustNone)
            .Columns.Item(4).SetWidth(111, Word.WdRulerStyle.wdAdjustNone)
            ' Set the shading on the first row to light gray.
            .Rows.Item(1).Cells.Shading.BackgroundPatternColorIndex = _
             Word.WdColorIndex.wdGray25
            ' Bold the first row.
            .Rows.Item(1).Range.Bold = True
            ' Center the text in Cell (1,1).
            .Cell(1, 1).Range.Paragraphs.Alignment = _
                      Word.WdParagraphAlignment.wdAlignParagraphCenter

            ' Fill each row of the table with data.
            FillRow(wrdDoc, 1, "Class Number", "Class Name", "Class Time", _
               "Instructor")
            FillRow(wrdDoc, 2, "EE220", "Introduction to Electronics II", _
                      "1:00-2:00 M,W,F", "Dr. Jensen")
            FillRow(wrdDoc, 3, "EE230", "Electromagnetic Field Theory I", _
                      "10:00-11:30 T,T", "Dr. Crump")
            FillRow(wrdDoc, 4, "EE300", "Feedback Control Systems", _
                      "9:00-10:00 M,W,F", "Dr. Murdy")
            FillRow(wrdDoc, 5, "EE325", "Advanced Digital Design", _
                      "9:00-10:30 T,T", "Dr. Alley")
            FillRow(wrdDoc, 6, "EE350", "Advanced Communication Systems", _
                      "9:00-10:30 T,T", "Dr. Taylor")
            FillRow(wrdDoc, 7, "EE400", "Advanced Microwave Theory", _
                      "1:00-2:30 T,T", "Dr. Lee")
            FillRow(wrdDoc, 8, "EE450", "Plasma Theory", _
                      "1:00-2:00 M,W,F", "Dr. Davis")
            FillRow(wrdDoc, 9, "EE500", "Principles of VLSI Design", _
                      "3:00-4:00 M,W,F", "Dr. Ellison")
        End With

        ' Go to the end of the document.
        wrdApp.Selection.GoTo(Word.WdGoToItem.wdGoToLine, _
                   Word.WdGoToDirection.wdGoToLast)

        InsertLines(2)

        ' Create a string and insert it into the document.
        StrToAdd = "For additional information regarding the " & _
                   "Department of Electrical Engineering, " & _
                   "you can visit our Web site at "
        wrdSelection.TypeText(StrToAdd)
        ' Insert a hyperlink to the Web page.
        wrdSelection.Hyperlinks.Add(Anchor:=wrdSelection.Range, _
           Address:="http://www.ee.stateu.tld")
        ' Create a string and insert it in the document.
        StrToAdd = ".  Thank you for your interest in the classes " & _
                   "offered in the Department of Electrical " & _
                   "Engineering.  If you have any other questions, " & _
                   "please feel free to give us a call at " & _
                   "555-1212." & vbCr & vbCr & _
                   "Sincerely," & vbCr & vbCr & _
                   "Kathryn M. Hinsch" & vbCr & _
                   "Department of Electrical Engineering" & vbCr
        wrdSelection.TypeText(StrToAdd)

        ' Perform mail merge.
        wrdMailMerge.Destination = _
                   Word.WdMailMergeDestination.wdSendToNewDocument
        wrdMailMerge.Execute(False)

        ' Close the original form document.
        wrdDoc.Saved = True
        wrdDoc.Close(False)

        ' Release References.
        wrdSelection = Nothing
        wrdMailMerge = Nothing
        wrdMergeFields = Nothing
        wrdDoc = Nothing
        wrdApp = Nothing

        ' Clean up temp file.
        System.IO.File.Delete("C:\DataDoc.doc")
    End Sub

Open in new window


Thats an example, as you already have the template, you could use that template and mail merge.  Please let know if any questions.  

Cheers.
1
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41719371
The mailmerge code from MSDN above isn't super modular so I advise on writing your own code.  You open up a lot more business functionality with your own code, i.e.

Give users a data dictionary (or write a word addin that has the variables in it users can insert).  Then anyone can write their own templates using [@variable] names and have it become a generative document.

If you put your letter into the code, it also means any time someone wants to make a change, the programmer has to go in and do it (and you've got better things to do than edit and proof letters).
1
 

Author Comment

by:RIAS
ID: 41721052
Dustin,
its not working mate for my example.
Please find the doc attached.
WordMerge.docx
0
 

Author Comment

by:RIAS
ID: 41721056
Also it is a .doc file
0
 

Author Comment

by:RIAS
ID: 41721058
need to populate values from datatable

       oDoc.Content.Find.Execute(FindText:="[a]", ReplaceWith:="Tjij :" & dtSQL.Rows(1)(2).ToString, Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll)
0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 250 total points
ID: 41721102
>> If you put your letter into the code, it also means any time someone wants to make a change, the programmer has to go in and do it (and you've got better things to do than edit and proof letters).

You don't need to put your letter into the code. You could easily use your existing template and just merge the fields. Why reinvent the wheel? The example already mentioned, was just to some pointer to mail merge.

To use existing template you may use as in here (http://burnignorance.com/vb-net-tricks/working-with-mail-merge-word-document-using-vb-net/):

Dim objWord As New Object
objWord = CreateObject("Word.Application") ' Creating a word application
 
Dim objDoc As New Object
objDoc = CreateObject("Word.Document") ' Creating a word document object
 
objWord.application.WindowState = 0 ' set the word window in normal state (Const wdWindowStateNormal = 0)
objDoc = objWord.Documents.Add(DocsFolder.FullName & "\YOUR WORD DOC NAME") ' Add the mail merged document file
 
Dim NameBeforeWordMerge As String = objWord.ActiveDocument.Name
 
objWord.ActiveDocument.MailMerge.OpenDataSource("YOUR DATABASE PATH", _
               Connection:="dsn=DSN NAME; dbq=" & YOUR DATABASE PATH & ";", _
               sqlstatement:="select * from `" & tableName & "`")
 
With objWord.ActiveDocument.MailMerge
         .MailAsAttachment = False
         .MailAddressFieldName = ""
         .MailSubject = ""
        .SuppressBlankLines = True
         With .DataSource
               .FirstRecord = 1  ' Const wdDefaultFirstRecord = 1
               .LastRecord = -16 ' Const wdDefaultLastRecord = -16 (&HFFFFFFF0)
         End With
        .Execute(pause:=True)
End With
      
 Dim NameAfterWordMerge As String = objWord.ActiveDocument.Name
 
objWord.Documents(NameBeforeWordMerge ).Close(0) ' close the template doc
objWord.Documents(NameAfterWordMerge ).Activate() ' activate the mail merged doc after record population

Open in new window


 
HTH.
0
 

Author Closing Comment

by:RIAS
ID: 41724187
Cheers!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now