Go Premium for a chance to win a PS4. Enter to Win

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

VB and Mail Merge

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
JIMVOIGT
Asked:
JIMVOIGT
  • 2
  • 2
1 Solution
 
bitter_chickenCommented:
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
 
JIMVOIGTAuthor Commented:
Yes, this is a word macro. I need to make this hapen with any user intervention.
0
 
bitter_chickenCommented:
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
 
JIMVOIGTAuthor Commented:
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
 
ravinder_reddyCommented:
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

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.

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