Solved

Trying to set the settings of the active printer

Posted on 2014-07-28
11
592 Views
Last Modified: 2014-08-05
Hello,

I am trying to set the default settings  of the active printer so that the number of copies and the duplex mode can be set by vba from within access.  Basically the requirement is to print a notice letter as part of a batch file.  The letter is generic are are access reports. For some reason when the word document opens up, the copies setting is still at on and the duplex setting is not being set.  Variable gintCopies is set in the calling code.

Public Sub PrintExternalWordDocs(strDocPath As String)
Dim objWord As Object
Set objWord = CreateObject("Word.Application")

Application.Printer.Duplex = acPRDPHorizontal
Dim objPrinter As Printer
Set objPrinter = Application.Printer
objPrinter.Duplex = acPRDPHorizontal
objPrinter.Copies = gintCopies

With objWord
    .Visible = True
    .Activate
    '.WindowState = Maximize
    .Documents.Open (strDocPath) 
End With



 'Release object
 Set objWord = Nothing
End Sub

Open in new window

0
Comment
Question by:chtullu135
  • 6
  • 3
  • 2
11 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 40226247
Application.Printer is referring to the MS Access printer settings, and you have not instructed Word to use those settings.  You've created objPrinter, but don't use it anywhere.

Try adding a line here:
With objWord
    .Visible = True
    .Activate
    '.WindowState = Maximize
    .Documents.Open (strDocPath) 
    .Printer = objPrinter  '<<<<<<<<<<<<<<<<<
End With

Open in new window

0
 

Author Comment

by:chtullu135
ID: 40226427
I see what you mean Jack.  I'll give it a try as soon as I get to work.
0
 

Author Comment

by:chtullu135
ID: 40226631
Hello Jack,

I tried your suggestion but I received the following error message
"Object doesn't support this property or method"
 on the following line
.Printer = objPrinter
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 40226697
Sorry, I was thinking Word had a Printer object like Access does.

A quick check shows that this might be a bit more of a pain...  Word has an ActivePrinter property, but it takes a string (printer name) and doesn't give us access to the Printer object like we have in Access, so there's no means to set up the duplex printing there.

If you want, you can look into objWord.WordBasic.FilePrintSetup("printer name"), but I'm not sure that's what you're looking for.

Another way to do it is using the Win API:
http://support.microsoft.com/kb/230743
http://word.mvps.org/faqs/macrosvba/SetDuplexOnOff.htm

This can have problems with user permissions, depending on what environment you need to run it in (as noted in the support article above).

Alternatively, you can
1) create a separate print driver with those defaults already set up and use that via Word.ActivePrinter, or
2) if possible, create the desired report in Access and print from there

Sorry about that - I thought it was an easy fix (been a while since I played with Word).
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40226831
One way you can do this is to create a separate printer icon, preset for duplex printing and any other settings.  Then you just need to select that printer for the job, and return to the default printer afterwards.  Here is some code for Access:
Public Sub PrintToSpecificPrinter(strPrinter As String, strReport As String)
'Created by Helen Feddema 12-Feb-2010
'Last modified by Helen Feddema 12-Feb-2010

On Error GoTo ErrorHandler

   Dim prtCurrent As Printer
   Dim prtDefault As Printer
   
   'Save current default printer
   Set prtDefault = Application.Printer
   Debug.Print "Current default printer: " & prtDefault.DeviceName
   
   'Select a specific printer as new default printer
   Application.Printer = Printers(strPrinter)
   
   'Print the report
   DoCmd.OpenReport strReport
   
   'Set printer back to former default printer
   Application.Printer = prtDefault
      
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

================================

Public Function ListPrinters()
'Lists Access printer names as used in VBA code
'to the Immediate window
  
   Dim prt As Access.Printer
   
   For Each prt In Application.Printers
      Debug.Print prt.DeviceName
   Next prt
   
End Function

Open in new window

and here is some Word code:
Public Sub PrintToSpecificPrinter(strPrinter As String)
'Created by Helen Feddema 12-Feb-2010
'Last modified by Helen Feddema 12-Feb-2010

On Error GoTo ErrorHandler

   Dim strDefaultPrinter As String
   
   'Save current default printer
   strDefaultPrinter = Application.ActivePrinter
   Debug.Print "Current default printer: " & strDefaultPrinter
   
   'Select a specific printer as new default printer
   Application.ActivePrinter = strPrinter
   
   'Print the current document
   Application.ActiveDocument.PrintOut
   
   'Set printer back to former default printer
   Application.ActivePrinter = strDefaultPrinter
      
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:chtullu135
ID: 40227027
Hello Helen
I've modified my code to incorporate your suggestions but I'm still not able to print the document in duplex. I've tried setting manualduplexprint to true but I'm still not able to print the document as a two-sided document.  However, I am able to print the requisite number of copies.

Public Sub PrintExternalWordDocs(strDocPath As String)
Dim strLength As Integer
Dim strDocName As String
Dim objWord As Object
Set objWord = CreateObject("Word.Application")

'Application.ActivePrinter = DLookup("[[AssignedPrinter]", "[tblAssignedReportPrinters]", "[ReportName] = " & "General Notice letter")
Application.Printer.Duplex = acPRDPHorizontal
Dim objPrinter As Printer
Set objPrinter = Application.Printer
objPrinter.Duplex = acPRDPHorizontal
objPrinter.Copies = gintCopies

Dim strDefaultPrinter As String

With objWord
    strDefaultPrinter = objWord.ActivePrinter
     Debug.Print "Current default printer: " & strDefaultPrinter
     .ActivePrinter = objPrinter.DeviceName
    .Visible = True
    .Activate
    '.WindowState = Maximize
    .Documents.Open (strDocPath)
    
    strLength = InStrRev(strDocPath, "\")
    strDocName = Right(strDocPath, Len(strDocPath) - strLength)
    Debug.Print strDocName
    If MsgBox("Print " & gintCopies & " copies of " & strDocName, vbYesNo) = vbYes Then
        .ActiveDocument.PrintOut Copies:=gintCopies, ManualDuplexPrint:=True
    End If
    
End With

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40227116
Did you first create a new printer icon (in the Printers section of the Control Panel, set up for duplex?  That is the first step, then run the ListPrinters procedure (in Access) to get the printer name as used in code, and then you can run the PrintToSpecificPrinter Word VBA procedure, feeding it the name of the duplex printer.
You could modify the procedure to add a NoCopies argument, for printing the desired number of copies.
0
 

Author Comment

by:chtullu135
ID: 40228306
Thanks Helen,

I see what you mean and will give it a try.  I'm a little unclear about the NoCopies.  I've tried looking it up but I am a little unclear what it is.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40228968
NoCopies was just my suggestion for an argument of Integer type you could use with the procedure (meaning number of copies).  Then you could feed the procedure the number of copies to print as well as the printer name.  I see you already have a gintCopies variable, so you could just use that.
0
 

Author Comment

by:chtullu135
ID: 40228978
Okay, I understand.  Thanks for the clarification.
0
 

Author Closing Comment

by:chtullu135
ID: 40240995
I apologize for the delay Helen.  Thanks again.  You're idea was great.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now