VBA Word: Mail merge labels printing

[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

[macropod]

My first question is: Why you're using Word to print what is essentially a table, when Excel is ideally suited to doing the same on its own?

My second question is: why you wouldn't merge direct to the printer?

Finally, the reason you've got the duplicate first page is that you have:
If Dialogs(wdDialogFilePrint).Show <> -1 Then End
Basically what you're telling Word to do with this statement is to print the document or exit. But only after sending the print command do you merge the output to the printer.

[amitash]

My first question is: Why you're using Word to print what is essentially a table, when Excel is ideally suited to doing the same on its own?

My second question is: why you wouldn't merge direct to the printer?

Finally, the reason you've got the duplicate first page is that you have:
If Dialogs(wdDialogFilePrint).Show <> -1 Then End
Basically what you're telling Word to do with this statement is to print the document or exit. But only after sending the print command do you merge the output to the printer.

Hi,
(1) as I want to maintain the Label template in Word file to do mail merge
(2) & (3) I want to allow the user to choose different printer to print, thats why I want to display the File Print Dialog box. How can I display the File Print Dialog box, allowing user to select print and click OK to print, but without printing the duplicate first page? Thanks!

[macropod]

Your answer to (1) seems to amount to nothing more than 'because you can', not because there's any particular need to do it that way.

For (2), the simple answer is to merge the output to a new document before invoking the print dialogue. Apart from anything else, that give you the opportunity to review the merged data. After printing, you can programmatically close the document without saving if you don't want to keep a copy.

[amitash]

Hi, how to achieve (2) in my code? Thanks.

[amitash]

Hi, I have sorted it out, not sure if this is the best method.

Sub printAllRecords()
' Print all records in mail merge
Dim bPrintBackgroud As Boolean
Dim oMerged As Document

'Disable to display all the alerts
bPrintBackgroud = Options.PrintBackground
Options.PrintBackground = False
Application.DisplayAlerts = wdAlertsNone
'Print all records
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

Set oMerged = ActiveDocument
'Show the Print dialog box
Dialogs(wdDialogFilePrint).Show
oMerged.Close 0
'Restore all the alerts
Application.DisplayAlerts = wdAlertsAll
Options.PrintBackground = bPrintBackgroud

End Sub

[amitash]

BTW, for (1), how can I transform the code from Word to Excel to do the mail merge printing?

[macropod]

If you're doing it all in Excel, you don't use 'mailmerge', per se, at all.

You simply create a worksheet with the required cell sizes, manual page breaks and margins, write the data to them, print, then erase when done. In fact, if you're always printing all of the data, you don't even need a macro - simply use formulae that will automatically update the output sheet as required. For an example of using formulae for this, see attached.

Alternatively, create a worksheet with cells of the required size (sufficient for one page) and margins, then cyclically write the data to them, print, erase the data until done.