XL5: Can't Print Collated Copies

• Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel 5.0, when you print multiple copies of a document, you cannot automatically collate the pages unless the printer driver supports collation.

NOTE: In earlier versions of Microsoft Excel, a document can be collated when you print it (regardless of whether the printer driver supports collated printing).

This functionality was added in Microsoft Excel 7.0.

STATUS

This method of printing is by design to allow for faster printing.

WORKAROUNDS

In Microsoft Excel 5.0, to print collated copies of a file, do any of the following:

• Print the document to a file on your hard drive and copy the file to the printer multiple times from the MS-DOS prompt.

  -or-

• Write a macro to print the selected file multiple times. Note that this option will be very time consuming because Microsoft Excel has to print single copies of the file multiple times. It is much faster to print a file multiple times. (Sample code is provided below.

  -or-

• If your printer driver provides the option to print collated copies, use this option. You can set this option by choosing the Print command on the File menu and then choosing Printer Setup in Microsoft Excel to access the printer driver settings.

MORE INFORMATION

An example of collated printing is when you print two copies of a file such that page 1 and page 2 of the first copy are printed, and then page 1 and page 2 of the second copy are printed. In contrast, uncollated printing would print page 1, and then another page 1, and then page 2, and then another page 2.

To print from the DOS prompt try the following command:

    copy filename lpt1:/b

Printer drivers that have a Copies box in the Print dialog box may support collation. For example, most UNIDRV-based drivers support collation; some printer drivers, such as the Hewlett-Packard (HP) DeskJet, do not.

SAMPLE VISUAL BASIC FOR APPLICATIONS CODE

The following Visual Basic for Applications code allows you to print multiple copies.

'A procedure to print multiple copies
Sub Example()
   Ncopies = Application.InputBox _
   ("Please enter number of copies to print: ", _
    "Print Multiple Copies", 1, , , , , 1)
   For counter = 1 To Ncopies
      ActiveWindow.SelectedSheets.PrintOut Copies:=1
   Next
End Sub