Member Avatar for amitash

Hi, I have created a label template in Word and written a macro to print all records (see code below printAllRecords() and attachments) from an Excel datasource. The problem is when I press F2 (defined function key to print all records in my macro), it prints the 1st page once and then print all records (i.e. 1st page is printed twice). Anyone knows why and 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;Data Source=strFileExcel;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet 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 Sub
  • 2 Contributors
  • 1 Reply
  • 865 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

The duplicate first page is very likely coming from the Print dialog being shown before the merge is executed. When you show the Print dialog and click OK Word prints the active/template page; your macro then calls the mail-merge print, so the first sheet gets printed twice. Removing that dialog (or replacing it with a simple confirmation) stops the double print.

A safe, minimal change is to remove the call that launches the Print dialog and use a confirmation instead, for example:

If MsgBox("Print all labels now?", vbOKCancel) = vbCancel Then Exit Sub

After that, let the MailMerge execute to the printer. If you want the user to be able to change printer options and preview before printing, run the merge to a new document (send to a new document), let the user inspect it, then print the merged document manually. That avoids accidental double-printing from the dialog.

Note on 's suggestion: the PrintOut method shown is an Excel command and will only apply if you intend to print the Excel sheet itself (or if you run the macro from Excel). For a Word label merge you should use Word's MailMerge.Execute or merge-to-new-document workflow instead.

Also restore any suppressed alerts and background-print settings after the macro finishes, and test the change with a small subset of records first to confirm the duplicate is gone.

Member Avatar for AndreRet

You can use something like -

ActiveWindow.SelectedSheets.PrintOut Copies:=1 ''Or as many pages as you need...
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.