PDF ?

P

Paul Watkins

Hi
Is it possible to save an excel file to a pdf document, maybe using VB code?


Thanks

Paul
 
F

Frank Kabel

Hi Paul
you have to use a PDF converter/creator (e.g. Adobe Acrobat, etc.)

Frank
 
P

Paul Watkins

Thanks for the reply
I have Adobe Acrobat installed on my Pc, what i need is a piece of VB code
that will be placed in macro that will save a sheet from a workbook to a
PDF document


Thanks
Paul
 
F

Frank Kabel

Hi Paul
the easiest way to get this code: Try recording a sample printout with
the macro recorder. Youl will get a result similar to the following
Sub Macro1()
Application.ActivePrinter = "Acrobat Distiller auf Ne04:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat Distiller auf Ne04:", Collate:=True
End Sub

So for a more generalized approach try using:
Sub print_selected_pdf ()
Dim Printer_name as string
Printer_name = "Acrobat Distiller auf Ne04:" 'change
this according to your system
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=
printer_name
end sub

Frank
 
P

Paul Watkins

Thanks Frank
I've tried the code, but it pops up a SaveAs (pdf) dialog box asking for a
filename.
Is there any way to incorporate a filename (test) into this code?.

Thanks


Paul
 
F

Frank Kabel

Hi Paul
try the following

Printtofile:=True, PrToFileName:="D:\temp\tester.pdf"
Sub print_selected_pdf ()
Dim Printer_name as string
Dim outputfile as string
outputfile="C:\temp\test.pdf"
Printer_name = "Acrobat Distiller auf Ne04:" 'change
this according to your system
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:=printer_name,Printtofile:=True, PrToFileName:=outputfile
end sub

you may have to disable some options within the properties for your
Adobe Acrobat printer. Goto 'File - Print'. Choose the properties of
the Adobe Distiller printer. uncheck 'Send fonts to Distiller' and 'Ask
for filename' (Note: These options may have a different name as I use
the German Adobe 5.0 version). Experment a little bit :)
Frank
 
P

Paul Watkins

Thanks again Frank
The code runs and creates a PDF file however when i try to open it, it is
'corrupt'. When i print to PDF manually it;s Ok
Any ideas?

Thanks

Paul
 
F

Frank Kabel

Hi Paul
difficult to say. Works for me (tested it with some worksheets). Could
be one of the settings, etc.

Frank
 
T

Tobin Gorey

'Lo Frank

I've tried using your code, but I keep getting a dialog
box with the message "Compile error. Named argument not
found"

What am I doing wrong?

Cheers

PS I'm a novice VB user if you haven't already guessed
that!
 
F

Frank Kabel

Hi Tobin
which line causes this error (maybe it's a problem with a linebreak
within one comment)
 
D

Dave Peterson

Could it be that prToFileName was added in xl2k--and not available in xl97?

What version of excel are you using?
 
G

Guest

'Lo Frank,

This the code I'm trying to run. It trips up on
the "PrToFileName" line. Hope this is helpful.

Cheers

Sub PrintPriceSheetsA()
'
' PrintPriceSheetsA Macro
' Tobin Gorey
'
'
Dim newfilename As String
Dim directory As String

directory = "G:\Commodities\Agri - softs\daily
argisoftsheets\test"

ChDir "G:\Commodities\Agri - softs\daily
argisoftsheets\test"

Sheets("WC WA").Select
newfilename = directory & Range("newfilename").Value
Application.ActivePrinter = "Acrobat Distiller on
Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:= _
"Acrobat Distiller on Ne01:", Collate:=True,
Printtofile:=True, PrToFileName:="newfilename"

End Sub
 
D

Dave Peterson

That's what I was trying to say!!!

<vbg>

Thanks for the straightforward clarification.
 
F

Frank Kabel

Tobin
your problem lies probably in the following:
1. In the line:
Application.ActivePrinter = "Acrobat Distiller on Ne01:"
you have to change this to your specific printer name

2. The following is ONE line (i have added another '_')
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:= _
"Acrobat Distiller on Ne01:", Collate:=True, _
Printtofile:=True, PrToFileName:="newfilename"
 
Top