?
Solved

Two MailMerge Print Jobs when there should be only one. VBA in Excel.

Posted on 2006-05-10
9
Medium Priority
?
276 Views
Last Modified: 2010-08-05
Does anyone know why this code sends two print jobs when run? The first print job is the template only... it shows the <<FIELDS>> instead of the data substitutes - <<NAME>> instead of Bob Smith. The second job follows immediately and prints the merge correctly. Is there a way to avoid wasting the extra sheet of paper for every time this gets run?

'*******Print the MailMerge*******'
    With ActiveDocument.MailMerge
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        Dialogs(wdDialogFilePrint).Show
        .Execute Pause:=False
    End With
'*********************************'

=====================The whole picture=================
Sub WordUp()
'=====================================
' WordUp Macro
'=====================================
' Purpose: Perform the MSWord MailMerge from this Macro
' Created: 4/25/06, Beau Holder
' Last Modified: 5/1/06 10:49am - Broke up longer lines to improve readability
'                               - changed directory to where the MailMerge Template is
'=====================================
'*******DECLARE VARIABLES*******'
    Dim appWd As Word.Application
    Dim WdDoc As Word.Document
    Dim sFile As String
'*******************************'
'*******Setup the Word application object*******'
    On Error Resume Next
        Set appWd = GetObject(, "Word.Application")
    If appWd Is Nothing Then
        Set appWd = GetObject("", "Word.Application")
    End If
    On Error GoTo 0
    sFile = fGetFilePath
'***********************************************'
'*******MailMerge Happens here*******'
    With appWd
        .Visible = True
        Set WdDoc = .Documents.Open(Filename:="c:\Doc.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
'************************************'
'*******Print the MailMerge*******'
    With ActiveDocument.MailMerge
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        Dialogs(wdDialogFilePrint).Show
        .Execute Pause:=False
    End With
'*********************************'
'*******Close the document now that the merge is done and quit Word*******'
    ActiveDocument.Close SaveChanges:=True
    appWd.Quit
'*************************************************************************'
'*******CLEAR OUT WORD OBJECTS*******'
    Set appWd = Nothing
    Set WdDoc = Nothing
'************************************'
End Sub

Thanks in advance.
0
Comment
Question by:Vriaeliss
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16655817
The mailmerge to to the printer should print the result document.
The show method with the print dialog will print the current document.
0
 

Author Comment

by:Vriaeliss
ID: 16657822
That clears things up a bit. Is there any way to supply the dialogue box without printing the current document? (added points for additional question asked)
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 450 total points
ID: 16658537
Yes. Use the Display method.
You'll need to find the 'Built-in Dialog Box Argument Lists' in the Word VBA help to know what parameters to capture and use.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Vriaeliss
ID: 16659029
Seems that using Dialogs(wdDialogFilePrint).Display instead of Dialogs(wdDialogFilePrint).Show fixed it. Thanks for helping Graham.

'*******Print the MailMerge*******'
    With ActiveDocument.MailMerge
        .Destination = wdSendToPrinter
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        Dialogs(wdDialogFilePrint).Display 'not .Show
        .Execute Pause:=False
    End With
'*********************************'
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16659083
It depends what you are trying to do with the dialog.
0
 

Author Comment

by:Vriaeliss
ID: 16659113
I'm just trying to get it to prompt the print dialogue so I can manually setup where the mail merge will be printed and be able to change the printer properties. A couple quick tests seemed to do that with that change. If you were referring to something else, I'd appreciate more detail (or some links to read up on).
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16659368
No. I just couldn't see how you were using the information from the dialog. However, it seems that the Display method can be used to change the default printer. It just doesn't print.
0
 

Author Comment

by:Vriaeliss
ID: 16659392
Ok, that makes sense, and it does what I want. the .Execute is what does the actual print job, I'm just happy that I can change the default printer and settings without printing the extra page ^_^
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16659523
The great thing about this forum is that we all learn, as I have just done.
Thanks and good luck.
0

Featured Post

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.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 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…
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

809 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