Solved

Trying to set the settings of the active printer

Posted on 2014-07-28
11
571 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

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.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

12 Experts available now in Live!

Get 1:1 Help Now