convert to pdf to excel using vba

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.

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.

:beerchug:

hi, can we protect this PDF with password also.