Application.Printer in a Macro

[Guest]

Before I ask my question, let me admit up front that I am VBA ignorant. I've
done programming in other languages, and I am trying to grasp the concepts of
VBA. I've looked all over this website, and Googled what I'm trying to do,
and I'm still just not quite getting it.

I have a macro that prints a set of about 10 or 15 different reports. Before
each report prints, I want the macro to call a VBA function that will set the
default printer to one specific printer. This is to make sure that when the
reports are printed using this macro, they print to the laser printer and not
to the Acrobat Distiller. Here is the code I tried in a VBA module, and which
doesn't work.

Function setPrinter(Application.Printer)
Set Application.Printer = Application.Printers("Acrobat Distiller")
End Function

This should be simple ... what am I doing wrong?

Many thanks,
GwenH

 

[Ken Snell [MVP]]

Tell us what "doesn't work" means. Show us how you've incorporated the
calling of the function into the macro actions.

 

[Guest]

When I attempt to run the macro that calls the function, I get an error
message. In the macro, I have a line that uses RunCode to call the function
by name, "setPrinter()". The error message says: "The Visual Basic Module
contains a syntax error. Check the code and then recompile it."

Also, the macro only has two lines: OpenReport and RunCode. Since I'm still
in the testing stage for the VB function, I only want to open the report and
set the default printer, so I can then go into File > Print and see if the
printer was set correctly.

Simple macro, simple function ... one befuddled VB newbie!

 

[Ken Snell [MVP]]

The error occurs because the macro isn't passing an argument to the
function. The function is expecting to receive an argument for
Application.Printer.

However, you don't need to have that in the function at all. Change the
function code to this:

Function setPrinter()
Set Application.Printer = Application.Printers("Acrobat Distiller")
End Function

Leave the macro as it is. It should work now.

 

[Guest]

Thank you, thank you, thank you! It works wonderfully.

Now let me ask you this: How complicated would it be to modify a macro that
opens in print preview, prints, and then closes a series of 10 or 15 reports
so that it prints each page of each report to a separate PDF file? These
reports are printed on a monthly basis, but the names of the PDF files are
the same every month.

If that would be simple, would you mind giving me the basic syntax?

Thanks again!

 

[Ken Snell [MVP]]

You want a macro to open a report, and print each page of the report to a
separate PDF file? No can do via macro.

You want a macro that opens a report, prints the report to a PDF file, and
then opens another report and prints that one to a PDF file? You can get the
macro to open the report and print to PDF (if you specify a PDF printer
driver before you open the report), but the macro won't be able to give the
"filename" of the PDF output to the printer process -- that will have to be
done manually in the printer window that the PDF driver likely opens to the
user.

 

[Guest]

Sorry, I should've explained that I want the macro to call one or more VB
functions that print each page of each report to a separate PDF file.

 

[Ken Snell [MVP]]

Each page of the report to be printed separately. I'm afraid that that is
not something I know how to do right now.... I know that it can be done
manually via the print window, but I'm not aware of a way to do it in VBA
code.. Doesn't mean it can't be done, but ....

I suggest that you post this question to the .reports newsgroup. Specify
that you're looking for a VBA programming method to print each individual
page of a report to a separate PDF file.

Perhaps someone there will have an idea for you.
--

Ken Snell
<MS ACCESS MVP>