Solved

VB and Mail Merge

Posted on 2004-04-24
5
987 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:bitter_chicken
ID: 10907738
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
ID: 10907812
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
ID: 10907996
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
ID: 10909939
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
ID: 11106731
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Recover unsaved Tab contents (and settings/preferences) from Notepad++ 3 150
convert Systemjs to Webpack 3 129
Do Wend Macro not working 22 72
Ruby or Python 7 132
A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.

751 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