Automating producing PDF of my Excel reports

[Chrisso]

Hi All

I have an Excel report that my boss wants to send via email to staff
who are not in the office often.

The report is rather large 1MB and is sent twice a day so he wants me
to send it as PDF and not Excel for size reasons.

However I cannot automate printing to PDF with Distiller as it always
produces a pop-up asking where to create the PDF and what name to give
to it. I need to do this all from my VBA code so there is no human
interaction.

Is this possible with Distiller or do I need some third party
software?

Thanks in advance for any pointers,
Chrisso

 

[GS]

Chrisso laid this down on his screen :

Hi All

I have an Excel report that my boss wants to send via email to staff
who are not in the office often.

The report is rather large 1MB and is sent twice a day so he wants me
to send it as PDF and not Excel for size reasons.

However I cannot automate printing to PDF with Distiller as it always
produces a pop-up asking where to create the PDF and what name to give
to it. I need to do this all from my VBA code so there is no human
interaction.

Is this possible with Distiller or do I need some third party
software?

Thanks in advance for any pointers,
Chrisso

Obviously you're not using Excel 2007 or 2010 or you'd see that this
capability is built in on the SaveAs menu. However, there are some PDF
printer drivers out there that allow some automation (limited) of their
component. Otherwise, licensing is required and the printer driver will
need to be distributed to (and properly registered on) all machines
your VBA project runs on.

Also, PDF file size is going to be an issue depending on which product
you use. Excel 2007 and 2010 can optimize final size if you check an
output option in the dialog.