none
How to code find and replace for multiple documents with different data on each template RRS feed

  • Question

  • Hi,

    I need to write a console app which will print a number of letters from a template, but each letter will contain different data in the find and replace.

    I'm using a stored procedure which retrieves the data into a datatable.

    I've had numerous attempts to get this code to work properly, and I understand the main problem, but have no idea how to correct it. The first letter works OK. but each subsequent iteration contains the same data. I need to close the document and reopen it, but at present this deletes the connection to my data in wordDoc.

    A second problem. When dateIndex reverts to 0, the whole sequence starts again and as long as the document isn't closed it will continue looping.

    Dim retentionDetails As List(Of ProjectRetentions_InitialClaim) = GetRetentionData()
    
        Dim wordApp As New Microsoft.Office.Interop.Word.Application
    
        Dim retentionInitial As String = My.Settings.TemplatePath & "\InitialRetentionTemplate.docx"
        Dim wordDoc As Microsoft.Office.Interop.Word.Document = wordApp.Documents.Open(retentionInitial)
    
        Dim dateIndex As Int32 = 0
    
        For Each range As Microsoft.Office.Interop.Word.Range In wordDoc.StoryRanges
    
          For dateIndex = 0 To retentionDetails.Count - 1
    
            wordApp.Documents.Open(retentionInitial)
    
            Dim detail As ProjectRetentions_InitialClaim = retentionDetails(dateIndex)
    
            Dim projectName As String = Convert.ToString(detail.ProjectName)
            Dim contractNumber As String = Convert.ToString(detail.ContractNumber)
            Dim clientReference As String = Convert.ToString(detail.ClientReference)
            Dim client As String = Convert.ToString(detail.Client)
            Dim projectManager As String = Convert.ToString(detail.ProjectManager)
            Dim retainedAmount As Decimal = Convert.ToDecimal(detail.RetainedAmount)
            Dim amount As Decimal = Convert.ToDecimal(detail.Amount)
            Dim finalDate As Date = Convert.ToDateTime(detail.FinalDate)
    
            range.Find.Text = "<<Client Name>>"
            range.Find.Replacement.Text = client
            range.Find.Wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue
            range.Find.Execute2007(Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll)
    
            wordApp.Visible = False
            'wordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone
            'wordDoc.PageSetup.Orientation = Word.WdOrientation.wdOrientPortrait
            'wordDoc.PageSetup.PaperSize = Word.WdPaperSize.wdPaperA4
            'wordDoc.PrintOut(, Collate:=False)
            wordApp.Documents(retentionInitial).PrintOut(Collate:=False)
            wordApp.Documents(retentionInitial).SaveAs("C:\Temp" & "\" & projectName & " " & "Initial Retention.docx")
            wordApp.Documents.Close()
    
    
            ' wordDoc1.Close()
          Next
        Next
    
    

    I've deleted a lot of the body of the code, which is not giving a problem. The commented code is just stuff I've already tried.

    Any help would be appreciated, as I think I'm using the wrong approach in this instance.

    Thanks

    John

     

     

    Saturday, January 29, 2011 10:25 PM

Answers

  • Hi Tailor

    At the risk of hearing you scream...

    <<I'm trying to code a console application to reside on my server, which will at a scheduled time, check for specific dates in an SQL table, then print  1 or more letters with the selected data from the table. I'm using VS2008 and Sql2005 Express with the app to run on an SBS2003 using scheduled tasks.

    It needs to be able to automatically happen without any user input.>>

    Given the above information, you probably shouldn't be trying to use the Word object model for this. Office apps weren't meant to be used server-side or unattended. Word tends to put up messages, asking for user confirmation, and there's no way to fully suppress them.

    More optimal (and loads faster) would be for you to leverage the Open XML file format, introduced for Office 2007. Since there's a compatibility pack available for Word 2000 - 2003 that lets them use the docx file format, this approach can be used for most corporate environments.

    You can find more information on OpenXMLDeveloper.org, and there's an Open XML SDK forum.

    Basically, you can put make a copy of  the template file (in this case a docx),  use the standard .NET packaging and XML namespaces to open this up, drop your data into the "markers", and put it in any location you like. You'll need the Word app to do the actual printing, but this could be done as a "batch" job after the documents have been generated.


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Tuesday, February 8, 2011 9:25 AM
    Monday, January 31, 2011 8:04 AM
    Moderator

All replies

  • Sounds like a task for mail merge to me.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Tailor" wrote in message news:59131359-0ba6-412e-84e0-f837e8251ad0@communitybridge.codeplex.com...

    Hi,

    I need to write a console app which will print a number of letters from a template, but each letter will contain different data in the find and replace.

    I'm using a stored procedure which retrieves the data into a datatable.

    I've had numerous attempts to get this code to work properly, and I understand the main problem, but have no idea how to correct it. The first letter works OK. but each subsequent iteration contains the same data. I need to close the document and reopen it, but at present this deletes the connection to my data in wordDoc.
    A second problem. When dateIndex reverts to 0, the whole sequence starts again and as long as the document isn't closed it will continue looping.


    Dim retentionDetails As List(Of ProjectRetentions_InitialClaim) = GetRetentionData() Dim wordApp As New Microsoft.Office.Interop.Word.Application Dim retentionInitial As String = My.Settings.TemplatePath & "\InitialRetentionTemplate.docx" Dim wordDoc As Microsoft.Office.Interop.Word.Document = wordApp.Documents.Open(retentionInitial) Dim dateIndex As Int32 = 0 For Each range As Microsoft.Office.Interop.Word.Range In wordDoc.StoryRanges For dateIndex = 0 To retentionDetails.Count - 1 wordApp.Documents.Open(retentionInitial) Dim detail As ProjectRetentions_InitialClaim = retentionDetails(dateIndex) Dim projectName As String = Convert.ToString(detail.ProjectName) Dim contractNumber As String = Convert.ToString(detail.ContractNumber) Dim clientReference As String = Convert.ToString(detail.ClientReference) Dim client As String = Convert.ToString(detail.Client) Dim projectManager As String = Convert.ToString(detail.ProjectManager) Dim retainedAmount As Decimal = Convert.ToDecimal(detail.RetainedAmount) Dim amount As Decimal = Convert.ToDecimal(detail.Amount) Dim finalDate As Date = Convert.ToDateTime(detail.FinalDate) range.Find.Text = "<<Client Name>>" range.Find.Replacement.Text = client range.Find.Wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue range.Find.Execute2007(Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll) wordApp.Visible = False 'wordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone 'wordDoc.PageSetup.Orientation = Word.WdOrientation.wdOrientPortrait 'wordDoc.PageSetup.PaperSize = Word.WdPaperSize.wdPaperA4 'wordDoc.PrintOut(, Collate:=False) wordApp.Documents(retentionInitial).PrintOut(Collate:=False) wordApp.Documents(retentionInitial).SaveAs("C:\Temp" & "\" & projectName & " " & "Initial Retention.docx") wordApp.Documents.Close() ' wordDoc1.Close() Next Next

    I've deleted a lot of the body of the code, which is not giving a problem. The commented code is just stuff I've already tried.
    Any help would be appreciated, as I think I'm using the wrong approach in this instance.
    Thanks
    John




    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Sunday, January 30, 2011 8:17 AM
  • Hi Doug,

    Mail Merge may be the answer, but I have not used it, even in Word itself, so am completely lost. All the reference I have found so far seem to be governed by user input .

    I am not sure what is needed here, other than it has to be fully automated, and run on SBS2003, without any user interaction. One of the filters ia a date and the app. will be activated by scheduled tasks.

    If it would be easier I can put the data into a CSV file in Excel format.

    I'm comfortably with the code up to the point of printing out the results. Thank you for your reply, but I feel I need a lot more information on how to achieve my goal here.

    Cheers

    John

     

     

    Sunday, January 30, 2011 9:52 PM
  • Hi John,

    Perhaps you could explain what you're trying to achieve? A Word mailmerge can work with a wide range of data sources, and the process is usually fairly straight-forward, especially when using Word's mailmerge wizard.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    Monday, January 31, 2011 3:45 AM
  • Hi Paul,

    I'm trying to code a console application to reside on my server, which will at a scheduled time, check for specific dates in an SQL table, then print  1 or more letters with the selected data from the table. I'm using VS2008 and Sql2005 Express with the app to run on an SBS2003 using scheduled tasks.

    It needs to be able to automatically happen without any user input.

    I have a Word document setup as a template, and have just modified it for mail merging with e.g. {MERGEFIELD Contract_Number} etc as placeholders. Until I can get it to run I don't even know if these place holders are correctly formatted.

    I've modified my code to save the data as a CSV file with the simple name "filename"  as a start but am not having any luck past that. I'll post the write to file code, and my pathetic start at the printing, which at this point can't find the data to print.

     

    Private fileName As String = String.Empty

     

    Private Sub WriteDataToFile(ByRef retention As List(Of ProjectRetentions_InitialClaim))

     

            fileName = String.Format("{0}\DataInitialRetention_{1}.csv", Directory.GetParent(Process.GetCurrentProcess().MainModule.FileName), Date.Now.ToString("ddMMyyyyhhmmss"), True)

     

            Dim itemsWroteToFile As Integer = 0

     

            LogMessage(String.Format("Creating new data file called {0}", fileName))

     

            Using sw As New StreamWriter(fileName)

     

                'Creating headings

                sw.WriteLine("BuildingId,ProjectName,ContractNumber,ClientReference,Client,ProjectManager,RetentionPercentId,RetainedAmount,Amount,FinalDate")

     

                'Write the data

                For Each item As ProjectRetentions_InitialClaim In retention

                    sw.WriteLine(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}", item.BuildingId, item.ProjectName.ToString, item.ContractNumber.ToString, item.ClientReference.ToString, item.Client.ToString, item.ProjectManager.ToString, item.RetentionPercentId, item.RetainedAmount, item.Amount, item.FinalDate))

                    itemsWroteToFile += 1

                Next

     

                sw.Close()

     

            End Using

     

            LogMessage(String.Format("Finished creating data file with {0} entries", itemsWroteToFile.ToString))

     

        End Sub

     

     

     

    Private Sub PrintMergeRetentions()

     

            Dim wordApp As New Microsoft.Office.Interop.Word.Application

     

            Dim wrdMailMerge As Word.MailMerge = Nothing

            ' Retrieve document template

            Dim retentionInitial As String = My.Settings.TemplatePath & "\InitialRetentionTemplate.docx"

            'Open document template

            Dim wordDoc As Microsoft.Office.Interop.Word.Document = wordApp.Documents.Open(retentionInitial)

            ' Select merge details from datatable

            Dim wordDoc1 As Word.WdMailMergeDataSource = Word.WdMailMergeDataSource.wdMergeInfoFromExcelDDE

     

            Dim wordDoc2 As Word.Document = Nothing

     

            wordDoc2.MailMerge.OpenDataSource(fileName)

     

            With wordDoc2

     

                wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters

                wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument

                wrdMailMerge.SuppressBlankLines = True

     

                With .DataSource

                    wrdMailMerge.DataSource.FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord

                    wrdMailMerge.DataSource.LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord

                End With

                wrdMailMerge.Execute(True)

            End With

            ' wordDoc.SaveAs("C:\Temp" & "\" & projectName & " " & "Initial Retention.docx")

            wordDoc2.Close(False)

            'wordApp.Quit()

     

        End Sub

    Hopefully this will give you some clues with which to assist me.

    Cheers

    John

    Monday, January 31, 2011 4:33 AM
  • Hi Tailor

    At the risk of hearing you scream...

    <<I'm trying to code a console application to reside on my server, which will at a scheduled time, check for specific dates in an SQL table, then print  1 or more letters with the selected data from the table. I'm using VS2008 and Sql2005 Express with the app to run on an SBS2003 using scheduled tasks.

    It needs to be able to automatically happen without any user input.>>

    Given the above information, you probably shouldn't be trying to use the Word object model for this. Office apps weren't meant to be used server-side or unattended. Word tends to put up messages, asking for user confirmation, and there's no way to fully suppress them.

    More optimal (and loads faster) would be for you to leverage the Open XML file format, introduced for Office 2007. Since there's a compatibility pack available for Word 2000 - 2003 that lets them use the docx file format, this approach can be used for most corporate environments.

    You can find more information on OpenXMLDeveloper.org, and there's an Open XML SDK forum.

    Basically, you can put make a copy of  the template file (in this case a docx),  use the standard .NET packaging and XML namespaces to open this up, drop your data into the "markers", and put it in any location you like. You'll need the Word app to do the actual printing, but this could be done as a "batch" job after the documents have been generated.


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Tuesday, February 8, 2011 9:25 AM
    Monday, January 31, 2011 8:04 AM
    Moderator
  • Hi Cindy,

    There is no screaming, just drip, drip, drip of blood as I slit my wrists :)

    I started to learn .net programming 4-5 years ago, and I'm fast running out of time to keep learning new things (I'll be 70 this year) but guess I have no option.

    I can use the docx format as I'm currently running Office 2007, so compatibility is no problem, so I guess it's off to another forum and many many more long nights for my tired eyes and brain.

    I appreciate you for taking the time to give this advice.

    Cheers

    John

     

     

     

     

    Monday, January 31, 2011 8:37 AM
  • Hi John

    I sympathize, I'm on the high-side of 50 going on 60 :-) Every now and then I ask myself, do I really want to try keeping up? And then I say, if you don't, you're getting old - do you feel old? The answer is always "not yet", so I keep on learning...

    If it's just a simple "data subsitution", as you describe, you should find a number of examples you can practically "plug in". Creating documents from scratch, with formatting, headers, footers, etc. is much more "challenging". But data substitution via a CustomXMLPart should be straight-forward enough to not be all too painful.

    "Throwing away" the code you've worked on up until now will probably hurt most. But you can keep the framework of that for the printing.


    Cindy Meister, VSTO/Word MVP
    Monday, January 31, 2011 9:14 AM
    Moderator
  • Hi Cindy,

    It's good to know you are not so young that you think at my age I should be sitting in a chair dribbling, and have no business messing with programming.

    Like yourself, I don't feel my age, and will keep learning new things as long as the mind holds out. Around 64(years old, that is) I needed a program for business so began to teach myself VS2005 and Sql, with books, forum help and help from a young friend in the UK. I've since graduated to a more challenging project using N-Tier architecture where the project has about 90 forms in the UI and a couple of thousand stored procedures. As well as that I manage the workstations and server, which probably isn't a bad effort for an old fart.

    I already have a console program based upon Excel which was simple in comparison to this, but I really need to have this Word one operating in the next few days.

    I've been searching the net for examples but so far have not been too successful, and probably not understanding what I need makes it harder. Most of the stuff I've found only gives a taste, or is straight XML. While I understand a little of the formatting of an XML document, putting it into practice would be a real challenge for me.

    All I can do is keep looking and hoping to stumble on a real world example.

    Throwing away code isn't a problem, I seem to have to do that all the time.

    Thank you for you time and words of encouragement, and keep on keeping on :)

    Cheers

    John 

    Monday, January 31, 2011 11:32 AM