Duplex Printing (Eng/XL97/SR2)

Topic

New Reply

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

Reply | Quote

Replies

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!

Reply | Quote

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.

Reply | Quote

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 Q135569, 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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 :

      If ExecuteExcel4Macro("Get.Document(50)") > 1 Then
          ' Code to set Duplex mode
      End if

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 :

    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 

to do what you want.

Andrew C

Reply | Quote