Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Need help converting Word Macro to be called in Excel

Posted on 2006-11-02
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

'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
Question by:Vriaeliss
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
  • 3
  • 3
LVL 30

Expert Comment

ID: 17879023
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.

Author Comment

ID: 17881059
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!

Author Comment

ID: 17885091
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"
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 30

Expert Comment

ID: 17886434
>> 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...............

Author Comment

ID: 17940096
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.
LVL 30

Accepted Solution

nmcdermaid earned 2000 total points
ID: 17945711
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.


Featured Post

Technology Partners: 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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

688 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