VBA Word: Mail merge labels printing

D

derricklo1980

Hi, I have created a label template in Word and written a macro to prin
all records (see code below printAllRecords() and attachments) from a
Excel datasource. The problem is when I press F2 (defined function ke
to print all records in my macro), it prints the 1st page once and the
print all records (i.e. 1st page is printed twice). Anyone knows why an
how to fix it?? Thanks!


Sub autoOpen()

Dim actPath As String
Dim strFileExcel As String
actPath = ActiveDocument.Path
strFileExcel = actPath + "\CCS Automation Template.xls"
' Get the source and update labels
ActiveDocument.MailMerge.OpenDataSource Name:= _
strFileExcel, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:=""
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Dat
Source=strFileExcel;Mode=Read;Extende
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Je
OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;J" _
, SQLStatement:="SELECT * FROM `Consolidate$`", SQLStatement1:="",

SubType:=wdMergeSubTypeAccess
WordBasic.MailMergePropagateLabel
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
With Application
'// Refer to THIS document for customisations
.CustomizationContext = ThisDocument

'// Add keybinding: F2
.KeyBindings.Add KeyCode:=BuildKeyCode(wdKeyF2), _
KeyCategory:=wdKeyCategoryCommand, _
Command:="printAllRecords"
End With
MsgBox "Press F2 button to print all records.", vbOKOnly, "Reminder


End Sub


Sub printAllRecords()
' Print all records in mail merge

Dim bPrintBackgroud As Boolean

'Disable to display all the alerts
bPrintBackgroud = Options.PrintBackground
Options.PrintBackground = False
Application.DisplayAlerts = wdAlertsNone

'Show the Print dialog box
If Dialogs(wdDialogFilePrint).Show -1 Then End
'Print all records
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

'Restore all the alerts
Application.DisplayAlerts = wdAlertsAll
Options.PrintBackground = bPrintBackgroud

End Su

+-------------------------------------------------------------------
|Filename: Label.zip
|Download: http://www.wordbanter.com/attachment.php?attachmentid=141
+-------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top