I have a macro that currently creates 800+ custom 2-page
reports for different vendors off a large database. The
first two tabs in my workbook represent the first and
second page of each report. The macro goes through the
list of 800 vendors, grabs the data that pertains to them
(located on different tabs of the workbook), places it in
an area that produces the graphs and summary charts for
that vendor, prints that vendor's report, erases the data
in the form, and proceeds to the next vendor.

I have been asked to produce PDF files for each vendor
instead of paper copies. I thought this would be an easy
conversion, but when the printer is set to PDF, a new
dialog box opens when the following code executes:

'Print page 1
Sheets("Report 1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
'Print page 2
Sheets("Report 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True

The dialog box is titled "Save PDF File As" and it
defaults to naming the PDF file with the first prefex as
the excel file. I can easily change that, except I can't
find any macro syntax that can either bypass this dialog
box by naming the file directly. I also can't figure out
how to write code that will accept the default file name
that is offered in this dialog box. I suspect that the
dialog box has been created by Adobe, but they can't help
me. This issue here is automation--I don't want someone
sitting at a computer pressing "yes" 800 times to accept
the default when I should be able to set a few parameters
within the subroutine and then walk away for four hours.

Any ideas?

I already approached Adobe thinking they would have
encountered this issue before, but they had no
suggestions.

In Excel 2000 and later, the printout method has two related arguments

PrintToFile:= specify true
PrToFileName:= specify the file name

Have you tried using those arguments?

--
Regards,
Tom Ogilvy

"Karl Schmidt" wrote in message
...
I have a macro that currently creates 800+ custom 2-page
reports for different vendors off a large database. The
first two tabs in my workbook represent the first and
second page of each report. The macro goes through the
list of 800 vendors, grabs the data that pertains to them
(located on different tabs of the workbook), places it in
an area that produces the graphs and summary charts for
that vendor, prints that vendor's report, erases the data
in the form, and proceeds to the next vendor.

I have been asked to produce PDF files for each vendor
instead of paper copies. I thought this would be an easy
conversion, but when the printer is set to PDF, a new
dialog box opens when the following code executes:

'Print page 1
Sheets("Report 1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
'Print page 2
Sheets("Report 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True

The dialog box is titled "Save PDF File As" and it
defaults to naming the PDF file with the first prefex as
the excel file. I can easily change that, except I can't
find any macro syntax that can either bypass this dialog
box by naming the file directly. I also can't figure out
how to write code that will accept the default file name
that is offered in this dialog box. I suspect that the
dialog box has been created by Adobe, but they can't help
me. This issue here is automation--I don't want someone
sitting at a computer pressing "yes" 800 times to accept
the default when I should be able to set a few parameters
within the subroutine and then walk away for four hours.

Any ideas?

I already approached Adobe thinking they would have
encountered this issue before, but they had no
suggestions.

Hi Tom,

Yes--I tried these first, but the problem is in the
conversion process. I can say
PrToFileName="vendor222.pdf" but the conversion doesn't
take place--the extension is pdf but the file type is
still xls when you open the new file. I can set up the
program to do that and create 800 excel files comprised
only of the two report pages and I can set up a string to
give each file a unique name matching its vendor, but
when I try to convert the stack of excel files to PDF
from inside Adobe I'm back in the same mess. When Adobe
starts the conversion process for a given file, it opens
Excel and you're eventually greated with the same "Save
PDF File As" dialog box with the default name written in
as "vendor222.pdf" and no way of pushing it through other
than clicking the "OK" button. It's the dialog box from
hell, because Excel produces it but doesn't seem to know
it exists when it comes to macro commands. Following the
MS naming conventions, I tried calling it "SavePDFFileAs"
in macro, but the VBA doesn't recognize it. Other ideas?

Karl
-----Original Message-----
In Excel 2000 and later, the printout method has two
related arguments

PrintToFile:= specify true
PrToFileName:= specify the file name

Have you tried using those arguments?

--
Regards,
Tom Ogilvy

"Karl Schmidt" wrote in
message
...
I have a macro that currently creates 800+ custom 2-
page
reports for different vendors off a large database. The
first two tabs in my workbook represent the first and
second page of each report. The macro goes through the
list of 800 vendors, grabs the data that pertains to
them
(located on different tabs of the workbook), places it
in
an area that produces the graphs and summary charts for
that vendor, prints that vendor's report, erases the
data
in the form, and proceeds to the next vendor.

I have been asked to produce PDF files for each vendor
instead of paper copies. I thought this would be an
easy
conversion, but when the printer is set to PDF, a new
dialog box opens when the following code executes:

'Print page 1
Sheets("Report 1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
'Print page 2
Sheets("Report 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True

The dialog box is titled "Save PDF File As" and it
defaults to naming the PDF file with the first prefex
as
the excel file. I can easily change that, except I
can't
find any macro syntax that can either bypass this
dialog
box by naming the file directly. I also can't figure
out
how to write code that will accept the default file
name
that is offered in this dialog box. I suspect that the
dialog box has been created by Adobe, but they can't
help
me. This issue here is automation--I don't want
someone
sitting at a computer pressing "yes" 800 times to
accept
the default when I should be able to set a few
parameters
within the subroutine and then walk away for four
hours.

Any ideas?

I already approached Adobe thinking they would have
encountered this issue before, but they had no
suggestions.


.

In article ,
says...
Hi Tom,

Yes--I tried these first, but the problem is in the
conversion process. I can say
PrToFileName="vendor222.pdf" but the conversion doesn't
take place--the extension is pdf but the file type is
still xls when you open the new file. I can set up the
program to do that and create 800 excel files comprised
only of the two report pages and I can set up a string to
give each file a unique name matching its vendor, but
when I try to convert the stack of excel files to PDF
from inside Adobe I'm back in the same mess. When Adobe
starts the conversion process for a given file, it opens
Excel and you're eventually greated with the same "Save
PDF File As" dialog box with the default name written in
as "vendor222.pdf" and no way of pushing it through other
than clicking the "OK" button. It's the dialog box from
hell, because Excel produces it but doesn't seem to know
it exists when it comes to macro commands. Following the
MS naming conventions, I tried calling it "SavePDFFileAs"
in macro, but the VBA doesn't recognize it. Other ideas?

Karl

You are going to need a PDF print driver, such as PDFPrinter, or Acrobat
Distiller to accomplish this. There are some ActiveX libraries out
there that will interact with VBA. Look at www.pdfstore.com. Search the
devlopment tools section.

If you are just printing to your normal printer and thinking putting PDF on
the end of the filename will work, that is not correct. You have to have
the PDFWriter printdriver set (from adobe acrobat not adobe acrobat reader
or an equivalent 3rd party driver).

--
Regards,
Tom Ogilvy



"Karl Schmidt" wrote in message
...
Hi Tom,

Yes--I tried these first, but the problem is in the
conversion process. I can say
PrToFileName="vendor222.pdf" but the conversion doesn't
take place--the extension is pdf but the file type is
still xls when you open the new file. I can set up the
program to do that and create 800 excel files comprised
only of the two report pages and I can set up a string to
give each file a unique name matching its vendor, but
when I try to convert the stack of excel files to PDF
from inside Adobe I'm back in the same mess. When Adobe
starts the conversion process for a given file, it opens
Excel and you're eventually greated with the same "Save
PDF File As" dialog box with the default name written in
as "vendor222.pdf" and no way of pushing it through other
than clicking the "OK" button. It's the dialog box from
hell, because Excel produces it but doesn't seem to know
it exists when it comes to macro commands. Following the
MS naming conventions, I tried calling it "SavePDFFileAs"
in macro, but the VBA doesn't recognize it. Other ideas?

Karl
-----Original Message-----
In Excel 2000 and later, the printout method has two
related arguments

PrintToFile:= specify true
PrToFileName:= specify the file name

Have you tried using those arguments?

--
Regards,
Tom Ogilvy

"Karl Schmidt" wrote in
message
...
I have a macro that currently creates 800+ custom 2-
page
reports for different vendors off a large database. The
first two tabs in my workbook represent the first and
second page of each report. The macro goes through the
list of 800 vendors, grabs the data that pertains to
them
(located on different tabs of the workbook), places it
in
an area that produces the graphs and summary charts for
that vendor, prints that vendor's report, erases the
data
in the form, and proceeds to the next vendor.

I have been asked to produce PDF files for each vendor
instead of paper copies. I thought this would be an
easy
conversion, but when the printer is set to PDF, a new
dialog box opens when the following code executes:

'Print page 1
Sheets("Report 1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
'Print page 2
Sheets("Report 2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True

The dialog box is titled "Save PDF File As" and it
defaults to naming the PDF file with the first prefex
as
the excel file. I can easily change that, except I
can't
find any macro syntax that can either bypass this
dialog
box by naming the file directly. I also can't figure
out
how to write code that will accept the default file
name
that is offered in this dialog box. I suspect that the
dialog box has been created by Adobe, but they can't
help
me. This issue here is automation--I don't want
someone
sitting at a computer pressing "yes" 800 times to
accept
the default when I should be able to set a few
parameters
within the subroutine and then walk away for four
hours.

Any ideas?

I already approached Adobe thinking they would have
encountered this issue before, but they had no
suggestions.


.