Vriaeliss
asked on
Two MailMerge Print Jobs when there should be only one. VBA in Excel.
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.do c")
ActiveDocument.MailMerge.O penDataSou rce Name:=sFile, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _
Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OL EDB.4.0;Pa ssword=""" ";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:=wdMergeSubTypeAcc ess
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.
'*******Print the MailMerge*******'
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
Dialogs(wdDialogFilePrint)
.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:=
ActiveDocument.MailMerge.O
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="",
Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OL
"Mode=Read;Extended Properties=""HDR=YES;IMEX=
"Jet OLEDB:Registry", SQLStatement:="SELECT * FROM `data`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAcc
End With
'*************************
'*******Print the MailMerge*******'
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
Dialogs(wdDialogFilePrint)
.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.
ASKER
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
'************************* ********'
'*******Print the MailMerge*******'
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
Dialogs(wdDialogFilePrint)
.Execute Pause:=False
End With
'*************************
It depends what you are trying to do with the dialog.
ASKER
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).
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.
ASKER
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 ^_^
The great thing about this forum is that we all learn, as I have just done.
Thanks and good luck.
Thanks and good luck.
The show method with the print dialog will print the current document.