Create a PDF file fromt a workbook file

[Ragnar Midtskogen]

Hello,

I need to create a pdf file from a workbook file using code.

I know how to do it by printing to PDFWriter or Distiller, but I was hoping
there is a simpler way.

I noticed that the Acrobat installer added a button to the Excel commandbar
for converting to PDF.
I tried recording a macro while doing a conversion, but there was nothing in
the macro so I guess
Adobe is using one of their PlugIns, as usual.

Any help would be appreciated.

Ragnar

 

[John Thow]

Hello,

I need to create a pdf file from a workbook file using code.

I know how to do it by printing to PDFWriter or Distiller, but I was hoping
there is a simpler way.

I noticed that the Acrobat installer added a button to the Excel commandbar
for converting to PDF.
I tried recording a macro while doing a conversion, but there was nothing in
the macro so I guess
Adobe is using one of their PlugIns, as usual.

Any help would be appreciated.

Ragnar

Don't know about PDFWriter... Could you use the Shell function to invoke it?

HTH

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Peo Sjoblom]

I wonder that too, the only methods I know is either printing to a pdf or
scanning to a pdf.

 

[Ragnar Midtskogen]

Hello John,

Thank you for responding.

PDFWriter is a program that comes with Acrobat (Not Acrobat Reader) that
installs itelf as a printer on your system.
When you use it to print something the result is a document file in PDF
format.
If you want to create a PDF file with code, you have to first use code to
ifrst capture the current default printer,
change the default printer to PDFWriter, print your document, then change
back to the original default printer.
I was hoping there was a way to hook into the Adobe Plugin set up for Excel
and have it take care of all that.

Ragnar

 

[Peo Sjoblom]

Why is that such a bother? If you change the printer first, print, then
change back?

Application.ActivePrinter = "PDFWriter"
'do your printing
Application.ActivePrinter = "HP DeskJet 882C"

 

[Ragnar Midtskogen]

Thank you Peo, made my day!

I was not aware that I could control the printer for Excel only, that IS
simple!

I have outputted Access reports as PDF files, but Access does not have a
similar property.
The choices are to use the report's PrtDevMode or to change the Windows
default printer,
neither of which is simple.

Ragnar

 

[Ragnar Midtskogen]

Hi Peo,

Guess what, your code:

Application.ActivePrinter = "PDFWriter"
'do your printing
Application.ActivePrinter = "HP DeskJet 882C"

gives an error message: Application.ActivePrinter Failed

I tried "Acrobat PDFWriter", which is how it is listed in the Print dialog,
but still error. I even tried setting it back to the default printer, which
I had captured in a string

Then I noticed soething even simpler, use the ActivePrinter argument
of the PrintOut method, and it works!

However, now the problem is that the File Save dialog keeps popping up
for every file.

How do I automatically accept the default file name?
I tried setting the PrToFileName argument but that produced an error message
that the
file was in use and locked.

Thanks,

Ragnar

 

[Peo Sjoblom]

This might help you

http://tinyurl.com/2leyq

or this

http://tinyurl.com/2p8ys

Normally if you save an excel file you use

Application.Display Alerts=False
'do the save
Application.Display Alerts=True

I have never automated this so I am not sure, best I could come up with are
those links

 

[Ragnar Midtskogen]

Than you Peo,

Normally if you save an excel file you use

Application.Display Alerts=False
'do the save
Application.Display Alerts=True

I have never automated this so I am not sure, best I could come up with are
those links

I had tried that, didn't work.

As for the links, I just remembered that in another application I had used a
Windows API call WriteProfileString to set the name of a PDF file in the
Registry because I needed to change the name. I just didn't stop and think
that this would get rid of the dialog.until I saw what was described in your
links.
Particularly with newer versions of Windows the Registry is the preferred
method for this kind of operations.

Ragnar

 

[Ragnar Midtskogen]

Oops, typo,

The API call was RegSetValueEx.

Ragnar