A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Microsoft Word Help
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VBA Word: Mail merge labels printing



 
 
Thread Tools Display Modes
  #1  
Old August 13th 12, 09:49 AM
derricklo1980 derricklo1980 is offline
Junior Member
 
First recorded activity by WordBanter: Jul 2012
Posts: 2
Default VBA Word: Mail merge labels printing

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!

[VBA]
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
[/VBA]
Attached Files
File Type: zip Label.zip (89.7 KB, 56 views)
Ads
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Diacritics and word template mail merge for printing labels sinojosh Microsoft Word Help 1 March 11th 09 04:31 PM
Printing Address Labels via Mail Merge in Word 2003 annasue Microsoft Word Help 4 September 10th 08 05:56 AM
Labels printing with Mail merge Roger Mailmerge 2 January 19th 08 10:31 PM
Printing Word 2000 labels from mail merge TerBear Microsoft Word Help 3 October 30th 07 01:25 PM
Labels Mail Merge Through VBA Shows Only One Page silvrwood Mailmerge 1 September 10th 07 09:16 PM


All times are GMT +1. The time now is 06:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2004-2013 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.