Select print Tray

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

I am using a Word mail merge template using my access database (2003) and
then calling the new document with the merged information back into access to
tell it to print. I have had to use the ActivePrinter command and switch from
one printer to another. This part is working good. I am now running into some
of the merged documents needing to be printed to letterhead or colored paper,
which would call for different print trays. I am unfamiliar with doing this.
Can anyone guide me in the right direction? I have tried to put a macro on
the template and new word documents to do this but no luck. It will change
printers but not trays. So I am hoping there is something I am missing in
Access that I can do. I have tried multiple different ways with no luck.
Below is the snippet of my code for viewing.
Any direction would help, I am lost and have tried multiple different things
but still nothing. I can switch printers but I cannot specify which tray to
print on that printer. Thank you ahead of time for any help.
A.J.

Public Sub cmdResponse_Click()
On Error GoTo Err_cmdResponse_Click
Dim dbs As Database, rst As Recordset, vCount As Integer
Dim objWord As Word.Document
Dim strPrinter As String

Set dbs = CurrentDb
DoCmd.SetWarnings False
If Me.txtOffice = "T" Then DoCmd.OpenQuery "qryNoticesTulsa" Else
DoCmd.OpenQuery "qryNotices"
End If
f Me.txtOffice = "T" Then
Set objWord =
GetObject("G:\Users\AmyC\WageHour\WordDocuments\Response2Tulsa.doc",
"Word.Document")
Else
Set objWord =
GetObject("G:\Users\AmyC\WageHour\WordDocuments\Response2.doc",
"Word.Document")
End If

Dim strDuplexPrint As String

objWord.Application.Visible = False

If Me.txtOffice = "T" Then
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\WageHour\WH.mdb",
LinkToSource:=True, Connection:="DSN=MS Access 97 -
WH;DBQ=G:\Users\AmyC\WageHour\WH.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5", SQLStatement:="SELECT *
FROM[tblNoticesTulsa]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
Else
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\WageHour\WH.mdb",
LinkToSource:=True, Connection:="DSN=MS Access 97 -
WH;DBQ=G:\Users\AmyC\WageHour\WH.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5", SQLStatement:="SELECT *
FROM[tblNotices]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
End If
'get the current printer information
strPrinter = objWord.Application.ActivePrinter
strDuplexPrint = "\\OKCL405\IT_PS.PRINTERS"
'change the printer to the duplex one
objWord.Application.ActivePrinter = strDuplexPrint
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
'change the printer back to the default after printing
objWord.Application.ActivePrinter = strPrinter
objWord.Application.ActiveDocument.Close (wdDoNotSaveChanges)
objWord.Close (wdDoNotSaveChanges)

Set objWord = Nothing
 
Take a look at the following links. Hopefully they will point you in the
right direction.

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=101
http://www.mrexcel.com/archive2/35000/40248.ht
http://www.exceltip.com/st/Select_a...rinting_using_VBA_in_Microsoft_Excel/458.html
--
Hope this helps,

Daniel Pineault
For Access Tips and Examples: http://www.cardaconsultants.com/en/msaccess.php
If this post was helpful, please rate it by using the vote buttons.



AJ said:
I am using a Word mail merge template using my access database (2003) and
then calling the new document with the merged information back into access to
tell it to print. I have had to use the ActivePrinter command and switch from
one printer to another. This part is working good. I am now running into some
of the merged documents needing to be printed to letterhead or colored paper,
which would call for different print trays. I am unfamiliar with doing this.
Can anyone guide me in the right direction? I have tried to put a macro on
the template and new word documents to do this but no luck. It will change
printers but not trays. So I am hoping there is something I am missing in
Access that I can do. I have tried multiple different ways with no luck.
Below is the snippet of my code for viewing.
Any direction would help, I am lost and have tried multiple different things
but still nothing. I can switch printers but I cannot specify which tray to
print on that printer. Thank you ahead of time for any help.
A.J.

Public Sub cmdResponse_Click()
On Error GoTo Err_cmdResponse_Click
Dim dbs As Database, rst As Recordset, vCount As Integer
Dim objWord As Word.Document
Dim strPrinter As String

Set dbs = CurrentDb
DoCmd.SetWarnings False
If Me.txtOffice = "T" Then DoCmd.OpenQuery "qryNoticesTulsa" Else
DoCmd.OpenQuery "qryNotices"
End If
f Me.txtOffice = "T" Then
Set objWord =
GetObject("G:\Users\AmyC\WageHour\WordDocuments\Response2Tulsa.doc",
"Word.Document")
Else
Set objWord =
GetObject("G:\Users\AmyC\WageHour\WordDocuments\Response2.doc",
"Word.Document")
End If

Dim strDuplexPrint As String

objWord.Application.Visible = False

If Me.txtOffice = "T" Then
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\WageHour\WH.mdb",
LinkToSource:=True, Connection:="DSN=MS Access 97 -
WH;DBQ=G:\Users\AmyC\WageHour\WH.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5", SQLStatement:="SELECT *
FROM[tblNoticesTulsa]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
Else
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\WageHour\WH.mdb",
LinkToSource:=True, Connection:="DSN=MS Access 97 -
WH;DBQ=G:\Users\AmyC\WageHour\WH.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5", SQLStatement:="SELECT *
FROM[tblNotices]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
End If
'get the current printer information
strPrinter = objWord.Application.ActivePrinter
strDuplexPrint = "\\OKCL405\IT_PS.PRINTERS"
'change the printer to the duplex one
objWord.Application.ActivePrinter = strDuplexPrint
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
'change the printer back to the default after printing
objWord.Application.ActivePrinter = strPrinter
objWord.Application.ActiveDocument.Close (wdDoNotSaveChanges)
objWord.Close (wdDoNotSaveChanges)

Set objWord = Nothing
Click to expand...
 
Back
Top