Solved

Need help converting Word Macro to be called in Excel

Posted on 2006-11-02
6
342 Views
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
    Else
        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.  
    Do
        Selection.ExtendMode = True
        Selection.Find.Forward = True
        Selection.Find.Execute FindText:=x$
        If Selection.Find.Found = False Then Exit Do
        Selection.Find.Execute
        If Selection.Find.Found = False Then
            Selection.EndOf Unit:=wdStory, Extend:=wdExtend
        Else
            Selection.Find.Forward = False
            Selection.Find.Execute FindText:="^b"
        End If
        Application.PrintOut Range:=wdPrintSelection, Background:=False
        Selection.ExtendMode = False
        Selection.MoveRight Count:=2
    Loop

End Sub

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

Thanks in Advance!
 ~ Beau
0
Comment
Question by:Vriaeliss
  • 3
  • 3
6 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
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.
0
 

Author Comment

by:Vriaeliss
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:="", _
            SubType:=wdMergeSubTypeAccess
    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!
0
 

Author Comment

by:Vriaeliss
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"
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 30

Expert Comment

by:nmcdermaid
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:

Selection.Find.ClearFormatting

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:

YourWordDocument.Selection.Find.ClearFormatting


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...............
0
 

Author Comment

by:Vriaeliss
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.
0
 
LVL 30

Accepted Solution

by:
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:

appWd.Selection.Find.ClearFormatting


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


Sorry for any confusion.

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now