We help IT Professionals succeed at work.

Need help converting Word Macro to be called in Excel

Vriaeliss asked
Medium Priority
Last Modified: 2008-02-01
More fun with MailMerge Macros and printing them...

I basically need to print mail merges one "record" at a time, so printer preferences can be applied individually. The company we work with sent us this Word Macro that gives me a "Run-time error 450" when it hits the first Selection.Find. I suspect this is because Excel doesn't have the Selection object like Word does... but I've gotten pretty rusty since I wrote the Excel Macros a while back.

Is there an easy/simple(ish) way to get this macro, or the similar effect, to work in excel? I've included the author's comments (which are outside the macro) for each section above each section.

Sub MailMergePrint()
' MailMergePrint Macro
' Written 10/3/2003 by Len Hoyt
' lhoyt@ikon.com
' lenhoyt@comcast.net

'get the text to mark beginning of next record
    x$ = InputBox("Enter a character or sequence of characters that" & Chr(13) & _
        "appears on Page 1, but only on Page 1, of each record" & Chr(13) & Chr(13) & _
        "Note: Do not use header or footer text.", "Search Text")

'no flicker
    Application.ScreenUpdating = False
 'move cursor to start of document  
    Rem Selection.HomeKey Unit:=wdStory

'The next lines, all the way to ‘End With’ set up parameters for the upcoming search.  These lines may not always be necessary since they’re defaults anyway, but it’s good housekeeping just in case parameters have been changed during a previous search.

    Selection.Find.ClearFormatting 'Error hits here
    With Selection.Find
        .Wrap = False
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With

 'The lines ending with “End If” determine the starting point of the print job within the document.  They back the cursor up to the first previous section break, or to the beginning of the document, whichever comes first  

    Selection.Find.Forward = False
    Selection.Find.Execute FindText:="^b"
    If Selection.Find.Found = False Then
        Selection.StartOf Unit:=wdStory
        Selection.MoveRight Count:=1
    End If
'The Do Loop is where the work is actually done.  The loop always begins with the cursor on the first line of a page, either because it’s been forced to the beginning of the document or current page when the macro begins, or because this Do Loop has moved it there after printing a record.  First, the loop turns on Word’s ‘Extend’ mode, so it can highlight all the pages of the current record.  Now, it finds the next two instances of the search characters.  The first instance occurs in the current record, and is ignored.  The second instance identifies the first page of the next record, so when it’s found, the Do Loop backs the cursor up (and so, the highlighted area) to the first previous section break.  At this point, all the pages in the current record are highlighted.  The loop sends the selected pages to the printer, and the print driver’s settings are applied.  When the job has been sent, the cursor position is advanced to the beginning of the next page.  The process is repeated until the loop doesn’t find a second instance of the search characters.  This occurs when the cursor is in the last record, so the loop simply selects all pages to the end of the document.  
        Selection.ExtendMode = True
        Selection.Find.Forward = True
        Selection.Find.Execute FindText:=x$
        If Selection.Find.Found = False Then Exit Do
        If Selection.Find.Found = False Then
            Selection.EndOf Unit:=wdStory, Extend:=wdExtend
            Selection.Find.Forward = False
            Selection.Find.Execute FindText:="^b"
        End If
        Application.PrintOut Range:=wdPrintSelection, Background:=False
        Selection.ExtendMode = False
        Selection.MoveRight Count:=2

End Sub

500 Points mostly for urgency... and it may be more difficult than I'm aware.

Thanks in Advance!
 ~ Beau
Watch Question

So there must be a Word file somewhere that contains the formatting you want?

The outline of what needs to be done is:

1. Add MS Word your references
2. Open your word merge file and assign it to a Word object
3. Now you can run all of those commands on the Word object.

If you're still around, post abck and I'll give more info.


Yup. I'm still here. Thank you for getting back.

I perform the mailmerge with this:
'*******MailMerge Happens here*******'
    With appWd
        .Visible = True
        Set WdDoc = .Documents.Open(Filename:="file.doc")
        ActiveDocument.MailMerge.OpenDataSource Name:=sFile, 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=sFile;" & _
            "Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";" & _
            "Jet OLEDB:Registry", SQLStatement:="SELECT * FROM `data`", SQLStatement1:="", _
    End With

Am I still able to use the WdDoc object after the "end with" or do I need to setup the Word object outside of the With?

I'll try adding MSWord to my references too.

Thanks again!


Just for a little more info: MS Word 10.0 Object Library is loaded. The immediately above code is in the same sub (directly preceeding in fact) the code in my question post.

I get an error when trying WdDoc.Selection.Find.ClearFormatting... I get: Run-Time 438 "Object does not support this property or method"
>> Am I still able to use the WdDoc object after the "end with" or do I need to setup the Word object outside of the With?

Yes. The With just saves you having to type appWd on every line. In other words, inside the With, you need to type this:

   .Visible = True

outside the With you need to type this:

   appWd.Visible = True

Its just a coding shortcut.

Regarding your error.

As you are running this in Excel, it assumes this line:


Is being run on the current Excel document.

In actual fact, that code snippet has to be run on a Word document.

To do this you needto fully qualify the code like this:


and the next obvious question is.... how does it know which word document we are working on?

You need a line beforehand which sets a reference to the Word document we are working on.

Let me just verify the actual code required for you...............


Just want you to know that I appreciate your help, and I haven't forgotten about you ^_^ Just really busy here... I'll try adding a reference to the word doc when I get a few spare minutes, and we'll see how that goes.

If you could suggest a particular line(s) to set this reference up, that'd be awesome.
OK I just reread the thread.

>> get an error when trying WdDoc.Selection.Find.ClearFormatting

Try this instead:


It looks like you need to reference the word 'application' at the root as opposed to the document.

Sorry for any confusion.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.