Printing to PDF
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 |
Printing to PDF
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 |
Printing to PDF
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 |
All times are GMT +1. The time now is 05:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com