When a user saves a workbook, I need to record the specific print settings
for that workbook.

I am already recording the application.activeprinter as well as the
activesheet.pagesetup settings, however I need to also record the print
range, the number of copies as well as the collate settings, and the "print
what" settings from the xldialogprint object. How can I access these
settings?

Thanks,
Alex

Turn on the macro recorder and then set up your print.

Turn off the macro recorder and look at the recorded code. Those are the
properties you can query direclty using VBA. After that, I would guess you
would need to get into the Windows API.

This article is about ACCESS, but should give some good insights.
http://msdn.microsoft.com/archive/en...softAccess.asp


http://support.microsoft.com/default...b;en-us;190218
HOWTO: Retrieve Settings From a Printer Driver

--
Regards,
Tom Ogilvy


"Alex281" wrote:

When a user saves a workbook, I need to record the specific print settings
for that workbook.

I am already recording the application.activeprinter as well as the
activesheet.pagesetup settings, however I need to also record the print
range, the number of copies as well as the collate settings, and the "print
what" settings from the xldialogprint object. How can I access these
settings?

Thanks,
Alex

I doubt you will be able to get everything directly from Excel/VBA. Unless
someone proves me wrong.
You could maybe hook the print dialog and grab the setting when it closes.

You could always replace Excel's dialog with your own (in the _BeforePrint
event), either userform or Windows dialog.
http://msdn.microsoft.com/archive/de...sdn_comdlg.asp

You could also monitor the print queue:
http://vb.mvps.org/samples/project.asp?id=PrnInfo

NickHK

"Alex281" ...
When a user saves a workbook, I need to record the specific print settings
for that workbook.

I am already recording the application.activeprinter as well as the
activesheet.pagesetup settings, however I need to also record the print
range, the number of copies as well as the collate settings, and the
"print
what" settings from the xldialogprint object. How can I access these
settings?

Thanks,
Alex

Thanks Tom and Nick,

I read the links that you sent.

The scenario I have is one where I cannot guarantee that the user will print
the workbook before closing it. If the user doesn't print the workbook and
he just simply saves it and closes it, I have no opportunity to acccess the
data that would be sent to the printer's driver.

I was trying to figure out a way to save the print settings before the
workbook gets closed. The reason that I need to gather that data is
because the workbook will be placed in a network folder and opened by a
different computer and automatically printed ... however if the user just
wanted to print a certain # of pages .. versus the whole workbook, then there
is no way to restrict that in the printout command if I don't know what the
user would have preferred... and the same applies to the number of copies as
well as the collate settings.

I didn't want to create my own form where the user would specify the print
settings because it would give the application a weird feeling. The user
(who may or may not be computer savvy) could get confused on whether to use
the Fileprint versus my custom form, or use both...
moreover, the user would also be using excel for other purposes other than
my application...

If you think of any way that I could get those settings, please let me know.

Thanks again

There are ways to monitor the print queue and check/count/cancel print jobs.
How a print job would relate to a range/worksheet/entire workbook, I not
sure; maybe you could find the number of pages in the WB and see if matches
the number of pages in the print job.
However, you would then have to cancel a job that the user thought had been
sent.

If you do find a way though, the user can still select all sheets and print
them togeter, without selecting entireWorkbook, or indeed print each page
separately to achieve the same.

Just wondering if it will be worth stopping it.

NickHK

"developer281" ...
Thanks Tom and Nick,

I read the links that you sent.

The scenario I have is one where I cannot guarantee that the user will
print
the workbook before closing it. If the user doesn't print the workbook
and
he just simply saves it and closes it, I have no opportunity to acccess
the
data that would be sent to the printer's driver.

I was trying to figure out a way to save the print settings before the
workbook gets closed. The reason that I need to gather that data is
because the workbook will be placed in a network folder and opened by a
different computer and automatically printed ... however if the user just
wanted to print a certain # of pages .. versus the whole workbook, then
there
is no way to restrict that in the printout command if I don't know what
the
user would have preferred... and the same applies to the number of copies
as
well as the collate settings.

I didn't want to create my own form where the user would specify the print
settings because it would give the application a weird feeling. The user
(who may or may not be computer savvy) could get confused on whether to
use
the Fileprint versus my custom form, or use both...
moreover, the user would also be using excel for other purposes other than
my application...

If you think of any way that I could get those settings, please let me
know.

Thanks again

You problem is that the Excel print dialog is shown after your _BeforePrint
code is run and Excel does not expose an of those settings or an _AfterPrint
event.
1- You could use FindWindow API etc to locate the handle of the dialog (it's
class name is "bosa_sdm_XL9"), but as most of the controls do not expose a
handle (according to Spy++), I doubt it would do you much good.

2- I have a class wrapper to the Windows PrintDialog API (by the KPD Team /
Donald Grover, cannot find a URL at the moment) which looks very similar to
Excel's dialog, but without the "Active Sheet(s)" and "Entire Workbook"
options. As you do not want people to print the Entire Workbook, this may be
suitable.
Otherwise, you can provide a PrintTemplate/PrintHook and make changes to the
dialog. Never done that so can't advise, but I doubt it is trivial.

3- If you go the print queue/jobs route
(http://vb.mvps.org/samples/project.asp?id=PrnInfo), you can only cancel the
job after the user thinks they are printing, which will certainly confuse
the user.

Before you start, you should decide if this is really necessary.

NickHK

"developer281" wrote in message
...
Thanks Tom and Nick,

I read the links that you sent.

The scenario I have is one where I cannot guarantee that the user will
print
the workbook before closing it. If the user doesn't print the workbook
and
he just simply saves it and closes it, I have no opportunity to acccess
the
data that would be sent to the printer's driver.

I was trying to figure out a way to save the print settings before the
workbook gets closed. The reason that I need to gather that data is
because the workbook will be placed in a network folder and opened by a
different computer and automatically printed ... however if the user just
wanted to print a certain # of pages .. versus the whole workbook, then
there
is no way to restrict that in the printout command if I don't know what
the
user would have preferred... and the same applies to the number of copies
as
well as the collate settings.

I didn't want to create my own form where the user would specify the print
settings because it would give the application a weird feeling. The user
(who may or may not be computer savvy) could get confused on whether to
use
the Fileprint versus my custom form, or use both...
moreover, the user would also be using excel for other purposes other than
my application...

If you think of any way that I could get those settings, please let me
know.

Thanks again