Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplex Printing (Eng/XL97/SR2)

    Is it possible to write vba code to recognize the number of pages to be printed. If more than one page turn the duplex feature of the printer on and print.

    Thanks,
    John

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    John,

    This seems a bit obvious, but why not set the printer on duplex all the time? Won't this do what you want?

    On the other hand I presume that you realize you will still have problems printing multiple sheets on duplex, as Excel always starts the next sheet as page 1, and hence on a new sheet. The only way I know around this would be to copy data to a separate printing sheet and print it all from there.

    Your original query about vba code, I think, requires that the specific printer driver be known, as specifying duplex is a component of the printer driver, not Excel.

    Hope this Helps.

    Good Luck!

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    There is a time element involved. If I change the printer to duplex all the time, each single page print job is passed through the printer as if there were more than one sheet; this is time consuming and does not make sense to print single pages this way.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    I don't know if this would be any help, but in the course of asking a question elsewhere on this board I came across something you might be able to use ... the {PRINT} field code can be used to switch duplex printing on and off - if you are using an HP printer, apparently you can put {print 27"&l#"} (use Ctrl-F9 to create the curly brackets, then type the rest yourself), where # is 0=duplex off, 1=duplex, long edge binding and 2=duplex, short edge binding. For more details, see <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q135/5/69.asp?LN=EN-GB&SD=gn&FR=0>Q135569</A>, as Andrew Connolly kindly pointed out to me.

    Unfortunately I still don't know how to use these in a macro, but I'm hoping either I'll work it out or someone will help me to do so! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Beryl M


  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    1,294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    HI John

    Our company - in MS-Word uses print code to print.
    thats all printing sent from word uses print code.
    The standard printer hardware & models we use are lexmark printers, HP's & other adhoc printers.

    Your question is 'can you print duplexing via code.'
    Yes you can.
    1stly the printer has to support duplexing.
    My next project is to print duplexing via code.
    I havent yet looked into or spent a great deal of time researching
    but when I do are you interested as I can hopefully assist you & can you wait until then?
    regards Diana

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    Diana,

    Thank you for responding. The printer that I use supports duplexing. I am familiar with the old dot matrix printer setup strings that allowed the user this type of flexibilty in reporting (printing) but unable to find docmumentation for VBA code.

    Any assistance you can provide is truly appreciate. I understand that your next project will focus on this and I am willing to wait.

    Thanks again,
    John

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    John,

    Is this what you are looking for?

    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
    "PRINT 27""&l2X""", PreserveFormatting:=False

    That field inserted into your document will download a PCL 5 printer command to print 2 copies of the document.

    The command to switch the printer to Duplex mode is:

    Text:="Print 27""&l1S"""

    Back to Simplex is:

    Text:="Print 27""&l0S"""

    0 = Simplex
    1 = Duplex, Long-Edge Binding
    2 = Duplex, Short-Edge Binding

    This info is taken from the HP PCL 5 Printer Language Technical Reference Manual.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Duplex Printing (Eng/XL97/SR2)

    John,

    By using the old Macro4 language, you can get the number of print pages in a workbook. The following is the syntax you probably want :<pre> If ExecuteExcel4Macro("Get.Document(50)") > 1 Then
    ' Code to set Duplex mode
    End if</pre>

    It would help to set up a second instance of your printer with the default mode set to Duplex (assuming the normal mode is Simplex) and give it a different name. So you would then have for example "MyStandard Printer" and "MyDuplex Printer". When using these names in VBA you need to include the name of the port the printer is attached to, for example the standard printer could be referred to as "MyStandard Printer on LPT1:". The best way to get correct reference is to record a macro where you select the printer. Having done that you could then use something like :<pre> Dim strPrinter As String
    strPrinter = Application.ActivePrinter
    If ExecuteExcel4Macro("Get.Document(50)") > 1 Then
    Application.ActivePrinter = "MyDuplex Printer on LPT1:"
    Else
    Application.ActivePrinter = "MyStandard Printer on LPT1:"
    End If
    ActiveSheet.PrintOut ' print the active sheet
    Application.ActivePrinter = strPrinter ' reset the default printer </pre>

    to do what you want.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •