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

Need help converting Word Macro to be called in Excel

Posted on 2006-11-02
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"
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

840 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