Print on PDF file

[Thanos]

Hello

is there anyone who knows how to create a PDF file of the active worksheet
through VBA code ?
I tried many examples found on the net with no success...

Cheers,
A.

 

[Michael Bednarek]

is there anyone who knows how to create a PDF file of the active worksheet
through VBA code ?
I tried many examples found on the net with no success...

If you have full Acrobat (not just the Reader), you can use several
methods:

1) Set the active printer to Distiller and simply use the method
..Printout - from memory, that will present a problem regarding the
filename selection

2) Adobe Distiller provides its own print method: FileToPDF.

If you don't have full Acrobat, you could use Ghostscript and RedMon.
Then you have to manipulate the RedMon registry entry which holds the
output filename. This will involve some nasty registry read/write
actions and dealings with the obscure way Excel uses printer names and
ports (different to any other MS Office application).

I have implemented the latter here, where a macro prints a number of
spreadsheets (>10) rapidly to PDF files (saving paper). However, about
once a week that process plays havoc with one particular PC's display:
no refresh in any window -including TaskMgr-, completely nuts.

Like you, I'm looking for a stable add-on which will provide the
equivalent to Adobe's FileToPDF; the full Acrobat is just to expensive
for that. The radical alternative, OpenOffice or Quattro, which both
provide native PDF output, is unfortunately not practical for us. It
might be for you, though.

Let us know if you find anything.

 

[Sharad]

Hello,

Try the method I use as under:

1. You have to first to some settings on the Adobe PDF printer. Open
Adobe PDF's printer properites from printer and faxes. Clik on General
Tab. There click on "Printing Preferences." Uncheck boxes 'View Adobe
PDF Results' and 'Prompt for Adobe PDF Filename'. Click on OK.
Click on 'Advanced' tab. There click on 'Printing Defaults'. Again
uncheck above mentioned two boxes.

2. Find the full name of the Adobe PDF Printer as recognized by excel
application. For this in excel open the file click on file menu-> Print.
Select Adobe PDF Printer and click on close.
In a module, add following code to display the printer name:
Sub showAdobePrinter()
MsgBox Application.ActivePrinter
End Sub.
Note down the full printer name displayed.
In my case it is 'Adobe PDF on Ne03'

3. Add following code to convert the sheet to PDF.
Sub MakePDF()
Dim lastPrinter As String, myPath As String
Dim myFileName As String, Wb As Workbook
myPath = "C:\Temp\" 'Only a temp. folder name (must exists)
myFileName = "xyz" 'desired pdf filename WITHOUT extension
myFullPath = myPath & myFileName & ".xls"
Worksheets("Sheet1").Copy 'Select your sheetname
Set Wb = ActiveWorkbook
Wb.SaveAs myFullPath
lastPrinter = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne03:"
Wb.PrintOut
Wb.Close (False)
Kill myFullPath
Application.ActivePrinter = lastPrinter
End Sub

The xyz.pdf file gets saved in the default folder path used by Adobe PDF
printer, defualt is My Documents.

Sharad

 

[Gord Dibben]

Michael mentions Ghostscript.

I have had success with the free file downloads and instructions for use here
at Dale Nurden's site.

The use of Ghostscript is the key point with this method.

http://www.rcis.co.za/dale/info/pdfguide.htm

Saves first to single *.ps file which is then converted to *.pdf

Returns one PDF file from multiple sheets.

Gord Dibben Excel MVP

 

[Michael Bednarek]

Michael mentions Ghostscript.

I have had success with the free file downloads and instructions for use here
at Dale Nurden's site.

The use of Ghostscript is the key point with this method.

http://www.rcis.co.za/dale/info/pdfguide.htm

Saves first to single *.ps file which is then converted to *.pdf

Returns one PDF file from multiple sheets.

That still does not cover the OP's (and mine) request for a method which
will work from VBA (without resorting to uglies like SendKeys).

So far, the only methods I have found involve modifying the PDF-creating
program's (Ghostscript, RedMod, PDFCreator) registry entries where they
store the filename and path. Printing to a fixed filename/path and
subsequent move/rename is probably also a possibility, but I haven't
explored that yet. I suspect it might be problematic with multiple print
jobs.

I would appreciate any pointers to a COM add-in interface to produce
PDF files.