I wrote a macro to automatically select sheets and print to a PDF
file. Is there a way to direct the pdf file to be saved to a certain
file each time(ie C:\My Documents\PDF)?

The Save as PDF box defaults to the last location I saved the excel
document or printed a pdf to (ie Desktop)


Thanks

John

John,

I take no credit for the code I found it (can't remember where)

Dim PDFFileName As String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
ActiveSheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Kill (PSFileName)

End Sub

Requires references to distiller and pdfmaker.

Dan E

"John" wrote in message om...
I wrote a macro to automatically select sheets and print to a PDF
file. Is there a way to direct the pdf file to be saved to a certain
file each time(ie C:\My Documents\PDF)?

The Save as PDF box defaults to the last location I saved the excel
document or printed a pdf to (ie Desktop)


Thanks

John

Sorry,

posted incomplete code

Private Sub CommandButton1_Click()
' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
ActiveSheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True,
prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Kill (PSFileName)

End Sub

If you wanted a different PDFFileName put in something like

ChDir ("C:\My Documents\PDF\")
PDFFileName = Application.GetSaveAsFilename("PDF Output.pdf", "PDF, *.pdf")
If PDFFileName = "False" Then
MsgBox Prompt:="PDF Not Saved, No name specified"
Exit Sub
End If

Dan E

"John" wrote in message om...
I wrote a macro to automatically select sheets and print to a PDF
file. Is there a way to direct the pdf file to be saved to a certain
file each time(ie C:\My Documents\PDF)?

The Save as PDF box defaults to the last location I saved the excel
document or printed a pdf to (ie Desktop)


Thanks

John