Solved

VB and Mail Merge

Posted on 2004-04-24
5
976 Views
Last Modified: 2013-11-13
I have a Vb Application which is written do a Mail Merge and Print to the printer and it is working fine.  This is now changed becuase the customer needs to have the Merged Document edited using a find and replace. I would still like to be able to print to the printer, buit I am having issues.

Any thoughts and can this be done.

Thanks
Jim Voigt
0
Comment
Question by:JIMVOIGT
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:bitter_chicken
Comment Utility
Is this a Microsoft Word macro?

If so, just merge to a new document (rather than merge to printer)
Then do the find/replace
Then print

If its not an MS Word macro - you'll have to provide some more details! Details details details!

bc :-)
0
 

Author Comment

by:JIMVOIGT
Comment Utility
Yes, this is a word macro. I need to make this hapen with any user intervention.
0
 
LVL 5

Accepted Solution

by:
bitter_chicken earned 50 total points
Comment Utility
to add find/replace functionality -

re-record the macro making sure to do the following:
When it prompts you, tell it you want to edit the letters or whatever individually, rather than printing directly
THEN print it for the sake of the of the macro... sound weird?

Anyway, once you have a macro that does that, bring up the macro code, and insert the following before the print, but after the mail merge code:


 Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = InputBox("Enter text to find...", "Find/Replace")
        .Replacement.Text = InputBox("Replace with:", "Find/Replace")
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

This will allow the user to pick what to find/replace, then it will print the file.

Note: You can make it loop the find/replace code; i.e. like this:

=========
userInputCheck = vbYes
While userInputCheck = vbYes

 Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = InputBox("Enter text to find...", "Find/Replace")
        .Replacement.Text = InputBox("Replace with:", "Find/Replace")
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

userInputCheck = MsgBox("Would you like to make another find/replace?",vbYesNo,"Find/Replace")

Wend

=========

My final macro looked like this (note; you should rebuild yours - otherwise the fields from the data source wont work right on the page)
====================================================================

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 24/04/2004 by Jack
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\datasource.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\datasource.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passw" _
        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
        wdFieldAddressBlock, Text:= _
        "\f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" & Chr(13) & "<<_COMPANY_" & Chr(13) & ">><<_STREET1_" & Chr(13) & ">><<_STREET2_" & Chr(13) & ">><<_CITY_>><<, _STATE_>><< _POSTAL_>>"" \l 3081 \c 0 \e """""
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
        wdFieldGreetingLine, Text:= _
        "\f ""<<_BEFORE_ Dear >><<_TITLE0_>><< _LAST0_>>" & Chr(13) & "<<_AFTER_ ,>>"" \l 3081 \e ""Dear Sir or Madam,"""
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
   
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = InputBox("Enter text to find...", "Find/Replace")
        .Replacement.Text = InputBox("Replace with:", "Find/Replace")
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
   
    ActiveWindow.View.DisplayPageBoundaries = Not ActiveWindow.View. _
        DisplayPageBoundaries
    ActiveDocument.PrintOut
End Sub
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/04/2004 by Jack
'
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = InputBox("Enter text to find...", "Find/Replace")
        .Replacement.Text = InputBox("Replace with:", "Find/Replace")
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

===================

hope this helps

bc :-)
0
 

Author Comment

by:JIMVOIGT
Comment Utility
Thanks Bitter Chicken,
I just needed to add the ActiveDocument.Printout, I quess I wasn't thinking correclty.  The only other thing I am tring to work out is, printing directly to the printer always had the Print Dialog Box waiting for a Printer Choice.  I am still looking into that.  

Thanks for the very fast comments


Jim
0
 
LVL 1

Expert Comment

by:ravinder_reddy
Comment Utility
Hai Jim ,

I am also trying for the same If u find a solution for ur problem, please mail to me
ravinder@ipointsoft.com

thanks & regards
ravinder@ipointsoft.com
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

762 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

11 Experts available now in Live!

Get 1:1 Help Now