HELP: Force Excel to print Entire Worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RJones - 19 Dec 2003 19:48 GMT

I have a small problem that I hope someone may be able to assist me with.

I am trying to force excel to automatically print an entire workbook, e.g.
if it contains many worksheets I want to automatically print everything.

Now I have tried using a macro that selects File--> Print--> Entire Workbook
and have saved this as a Personal Workbook Macro and it works fine for all
new workbooks, however I would like this to work for all workbooks
regardless who they belong to e.g. I would like all workbooks to inherit the
Personal Workbook Macro.

Can this be done, perhaps using VBA?

Any help would be welcome!

rj

Reply to this Message

Paul Cundle - 19 Dec 2003 21:22 GMT

First point: one assumes that selecting all sheets at once (by using
shift-click) is not an option. I can't imagine why, but mine is not to
reason why.

If you want a macro, I see no reason why you can't use a macro stored in
personal.xls as you have described. As long as the workbook is active (which
I suppose it must be if you're printing it!) then a command beginning:
ActiveWorkbook.
should work. If this is what you're doing, maybe you could send the code so
(I/we) can tell you what's wrong.

Paul C,

Signature

> I have a small problem that I hope someone may be able to assist me
> with.
[quoted text clipped - 14 lines]
>
> rj

Reply to this Message

RJones - 19 Dec 2003 22:06 GMT

> First point: one assumes that selecting all sheets at once (by using
> shift-click) is not an option. I can't imagine why, but mine is not to
[quoted text clipped - 6 lines]
> should work. If this is what you're doing, maybe you could send the code so
> (I/we) can tell you what's wrong.

Thanks for the reply Paul,

Firstly I must add that the reason I want the ability to print the entire
workbook is because I need excel to print everything automatically as I will
not have control over the excel session.

The session is running on a IIS server that is outputting the Print stream
through to a 3rd party document viewer, which is working fine apart from the
fact that it is only outputting the first sheet regardless of how many
sheets the workbook consists of.

OK, I have tried Personal.xls as described in my previous E-mail,
unfortunately this only works when the any new workbooks have focus, hence
any new - opened workbooks do not print all worksheets.

I need all subsequent workbooks to 'inherit' the macro that forces the
entire workbook to print.

Some nice person by the name of Vasant Nanavati in
microsoft.public.excel.worksheet.functions mentioned trapping the
WorkbookBeforePrint event at the Application level (more info here
www.cpearson.com/excel/appevent.htm).

Vasant provided an example VBA code, albeit I am a little green with VBA.

If anyone has the answer in the meantime, you are quite welcome in putting
me out of my misery LOL.

... watch this space.

Reply to this Message

Paul Cundle - 19 Dec 2003 23:31 GMT

>> First point: one assumes that selecting all sheets at once (by using
>> shift-click) is not an option. I can't imagine why, but mine is not
[quoted text clipped - 6 lines]
>> should work. If this is what you're doing, maybe you could send the
>> code so (I/we) can tell you what's wrong.

> The session is running on a IIS server that is outputting the Print
> stream through to a 3rd party document viewer, which is working fine
> apart from the fact that it is only outputting the first sheet
> regardless of how many sheets the workbook consists of.

Firstly: Blimey! A fellow bottom-quoter. Not many of those in this group :)

> OK, I have tried Personal.xls as described in my previous E-mail,
> unfortunately this only works when the any new workbooks have focus,
> hence any new - opened workbooks do not print all worksheets.

I don't really understand this point, TBH. The whole point of personal.xls
is that the macros contained within can be activated from any workbook at
any time, and applied to any open (or, indeed, not open) workbook.

> I need all subsequent workbooks to 'inherit' the macro that forces the
> entire workbook to print.

Again, if you mean by "inherit" that any workbook should be able to make use
of the macro, then this should be possible if the macro is stored in
personal.xls. The only time I can think this wouldn't work is if it begins:
Private Sub xxxxx()
but I doubt you've got that anyway.

> Some nice person by the name of Vasant Nanavati in
> microsoft.public.excel.worksheet.functions mentioned trapping the
[quoted text clipped - 3 lines]
> Vasant provided an example VBA code, albeit I am a little green with
> VBA.

Well I understand what the WorkbookBeforePrint event is used for, but I
really don't see why - if that event is to prove sufficient - any other
macro (not an event handler, but rather a straightforward "print all sheets"
macro) couldn't work just as efficiently.

I realise I'm probably not being much help here, but I will add something
that may be of use. People who try to print to 'document centres' with a
stapling function often find the stapling doesn't work properly, and the
reason for this appears to be that every time a sheet is encountered with a
different print setting (e.g. draft or B&W is set) Excel effectively
commences a new print job. You say you are outputting to a third party
viewer, and I suspect that something similar may be occurring - the first
sheet has a different setting to the second, meaning the remaining sheets
are sent as a different job and not parsed correctly to the viewer.

Maybe...

Paul C,
--

Reply to this Message

Rate this thread: