PDA

View Full Version : convert to pdf to excel using vba



coba
08-17-2008, 04:34 AM
I have acrobat 8 and would like to create a pdf file from excel using vba code (I am not very good at vba). I have several ranges and want to make every month a pdf file to send out. Is there a effective vba code for that? I have tried recording a macro, print, print range to a pdf. However, I do not achieve a good result.

I tried:
Sub createpdf()
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$B$2"
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub"


Thank you for your help.

Aussiebear
08-17-2008, 04:40 AM
Why hasn't Acrobat listed a shortcut Icon in your Excel?

coba
08-17-2008, 04:59 AM
and what about the macro to use?

Aussiebear
08-17-2008, 05:01 AM
So there's no icon?

coba
08-17-2008, 05:29 AM
yes, there is an icon. how do I avoid the repetitive task and just use a button to make the pdf file. of which i have different ranges to create. by clicking on the icon I am not able to select the range I need to print. So I am still a little bit lost trying to solve this.

coba
08-19-2008, 10:03 AM
Is there a way to choose the pdf printer? and a code to close pdf again?

Sohail
01-27-2012, 05:29 AM
Can any one help me how to use Pdfistiller ???

Dim myPDF As PdfDistiller

it gives me an errror user defined type not found..

frank_m
01-27-2012, 07:49 AM
Can any one help me how to use Pdfistiller ???

Dim myPDF As PdfDistiller

it gives me an errror user defined type not found.. You need to set Reference to the Acrobat Distiller (VB Editor Menu Bar > Tools > References)

Example
Option Explicit

Sub XLS_to_pdf()

Dim ConvertedPDFfilename As String, POSTSCRIPTfilename As String, sPrinter As String
Dim myPDF As PdfDistiller

Set myPDF = New PdfDistiller

'change paths to suit
POSTSCRIPTfilename = "c:\Test\" & "YourPDFfile" & "PostScriptFile.ps"

ConvertedPDFfilename = "c:\Test\" & "YourPDFfile" & ".pdf"

'Store the Active Printer name, so you can change back to it.
sPrinter = Application.ActivePrinter

'change to Distiller Printer name.
'(if you do not know the name, select it while recording a macro, then look at the code)
Application.ActivePrinter = "Acrobat Distiller on Ne02:"

'creates a Post Script File from the ActiveSheet.
ActiveSheet.PrintOut prtoFilename:=POSTSCRIPTfilename, PrintToFile:=False

'This converts .ps file, to .pdf
myPDF.FileToPDF POSTSCRIPTfilename, ConvertedPDFfilename, Chr(34)

'Deletes the Post Script File, as it was only needed to create the PDF from it.
Kill (POSTSCRIPTfilename)

'Open the PDF that was created
'Path differs with Acrobat version. - The space after Acrobat.exe, is necessary.
Shell "C:\Program Files\Adobe\Acrobat 5.0\Acrobat\Acrobat.exe " _
& ConvertedPDFfilename, vbNormalFocus

' change back to the printer that was active before running this routine
Application.ActivePrinter = sPrinter

Set myPDF = Nothing

End Sub

Kenneth Hobs
01-27-2012, 01:29 PM
I am not sure what "good result" means.

If you don't have distiller but do have Excel 2007+ then:


Sub Test_PublishToPDF()
Dim sDirectoryLocation As String, sName As String

sDirectoryLocation = ThisWorkbook.Path
sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf"
PublishToPDF sName, ActiveSheet
End Sub


Sub PublishToPDF(fName As String, ws As Worksheet)
Dim rc As Variant

'ChDrive "c:"
'ChDir GetFolderName(fName)
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If rc = "" Then Exit Sub

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub