convert to pdf to excel using vba

[coba]

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.

[Dr.K]

It all depends on HOW you want to print. From Excel, you have the complication that most printing is done at the Worksheet level, not the top-level document.

Make sure that you add a reference to "Acrobat Distiller" ("acrodist.exe") to your project, and then you can use this.

[vba]Sub ConvertWorkSheetToPDF(Optional InputWS As Worksheet, _
Optional PDFfilepath As String, Optional PDFfilename As String)

Dim objDist As New ACRODISTXLib.PdfDistiller


'**if no worksheet object, branch code to use ActiveSheet
If InputWS Is Nothing Then GoTo UseActivesheet


'If no file Path, use the parent Workbook path
If PDFfilepath = Empty Then
PDFfilepath = InputWS.Parent.Path & "\"
Else
If Not Right(PDFfilepath, 1) = "\" Then PDFfilepath = _
PDFfilepath & "\"
End If

'if no file name, build a name from workbook and sheet names
If PDFfilename = Empty Then PDFfilename = _
Left(InputWS.Parent.Name, Len(InputWS.Parent.Name) - 4) _
& " - " & InputWS.Name

'Print the worksheet as a postscript file (.ps)
InputWS.PrintOut Copies:=1, preview:=False, printtofile:=True, _
PrToFileName:=PDFfilepath & PDFfilename & ".ps", _
collate:=True, ActivePrinter:="Adobe PDF"
GoTo ConvertPS

UseActivesheet:
PDFfilepath = Environ("USERPROFILE") & "\Desktop\"

PDFfilename = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) _
& " - " & ActiveSheet.Name

'Print the worksheet as a postscript file (.ps)
ActiveSheet.PrintOut Copies:=1, preview:=False, printtofile:=True, _
PrToFileName:=PDFfilepath & PDFfilename & ".ps", _
collate:=True, ActivePrinter:="Adobe PDF"

ConvertPS:
'**Convert .ps file to .pdf and clean up
'use the Distiller to convert the .ps file to a .pdf
objDist.FileToPDF PDFfilepath & PDFfilename & ".ps", _
PDFfilepath & PDFfilename & ".pdf", ""

'delete the intermediate files
Kill (PDFfilepath & PDFfilename & ".ps")
Kill (PDFfilepath & PDFfilename & ".log")

'cleanup
Set objDist = Nothing
End Sub[/vba]

Also, you MUST have the default settings for your "Adobe PDF" printer set to:
Un-check the 'Do not send fonts to "Adobe PDF" ' box.

As far as I know, there is no way to change this setting programatically.

[susanjq]

[excelliot]

hi, can we protect this PDF with password also.