Printing several worksheets as _one_ print job

[Guest]

I use a VBA macro to print all worksheets of a workbook.

The code looks like this:
ActiveWorkbook.Sheets.PrintOut

I've noticed that Excel 2003 SP1 creates a different print job for each of
the WorkBook's WorkSheets. Since I use a printer driver that creates PDF
files out of print jobs I get three PDFs for three WorkSheets.
Is it possible to print all WorkSheets of a WorkBook into _one_ print job?
The reason is I want one PDF for the whole Excel file and not several.

Thanks,
Guido

 

[Peter Huang]

Hi Guido,

Have you tried to call the PrintOut method of the method directly?
Sub Test()
ActiveWorkbook.PrintOut
End Sub

In this way there will be only one print job. If you still have any concern
,please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John Mansfield]

You might try a variation of the following. In this
example, the for-next loop sends all of the visible sheets
to the printer as one job. The code can be easily
modified to your requirements.

Sub Print_Sheets()
Dim sht
Application.ScreenUpdating = False
For Each sht In Sheets
If sht.Visible Then sht.Select Replace:=False
Next
ActiveWindow.SelectedSheets.PrintOut copies:=1
ActiveSheet.Select
Application.ScreenUpdating = True
End Sub

Regards,

John Mansfield
The Planning Deskbook
http://www.pdbook.com

 

[Guest]

Thanks for your code. Unfortunately, my file that has three Worksheets still
produces three print jobs. However, another file with three Worksheets
produces only one print job. Some experiments revealed different page setups
(portrait/landscape, margins). Obviously this makes Excel begin a different
print job.

Guido

 

[Guest]

Well, to be precise: a different DPI setting (in page setup) for the
different WorkSheets made Excel produce different print jobs. It's not a bug
- it's a feature...

Guido

 

[Peter Huang]

Hi Guido,

Did you still have any concern on this issue?
If so please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

I tried the ActiveWorkbook.PrintOut macro and I get the same thing. I am
printing to a Xerox copier that has a computer front end that makes the
copier a printer. All of the worksheets are submitted to the frontend
computer as separate files. How can I get Excel to print all worksheets as
one job to the printer. My pages will not print in order.

Our work around is to "print all worksheets" and "output options print to
file". Then Distill the file to an Acrobate file.

Any Ideas?

Mike

 

[Guest]

Well,

The workaround does not work. We were using this workaround on the PC and it
does not act the same way on the Macintosh. Excell really needs better
control of it's printing process. You should be able to select a button that
sends all worksheets as one file.

 

[Guest]

I use a VBA macro to print all worksheets of a workbook.

The code looks like this:
ActiveWorkbook.Sheets.PrintOut

I've noticed that Excel 2003 SP1 creates a different print job for each of
the WorkBook's WorkSheets. Since I use a printer driver that creates PDF
files out of print jobs I get three PDFs for three WorkSheets.
Is it possible to print all WorkSheets of a WorkBook into _one_ print job?
The reason is I want one PDF for the whole Excel file and not several.

I found a fix to this problem, that works in Office 2000 anyway...

Each worksheet is created from a single worksheet.
Right click on the target worksheet, "Move or Copy", check "Make a Copy".

DON'T Insert->Worksheet.
DON'T populate more than a single worksheet that is to be printed from the
auto-generated ones.

Then format away. The result for me was a five page, single pdf, three
pages portrait, and two pages landscape (with graphics). Before applying
this trick, I had three individual pages (each as their own pdf) and a pair
of pages that had been copied one from the other (which was the clue)

Yes, it is a pain in the tush to copy the contents and the column widths
from existing spreadsheets, but it does pay off...

 

[Jerry@LWTPara]

--
Jerry and Connie
CEB