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


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
' 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
Question by:Vriaeliss
  • 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"
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

824 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