Print to PDF: PrintOut Method

[June1975]

Im trying to automate the saving of Worksheets to PDF from withing VBA Excel.

Im using the print out method below.

ActiveWindow.SelectedSheets.PrintOut copies:=1

Is there any way of automating the assigning of the filename without having to manually ok the print dialog?

I have multiple workbooks with worksheets so automating this would speed things up....

thanks

 

[KenPuls]

Hi there,

One thing you neglected to post was the program you're using to accomplish the PDF portion.

I have some examples of using PDFCreator, a free download, to do exactly what you're after. You can find the most popular one <a href=

http://excelguru.ca/node/21>here</a>.

It has examples of printing a single sheet, multiple sheets to a single file, or multiple sheets to multiple files.

HTH,

Ken Puls, CMA

http://www.excelguru.ca

 

[June1975]

....thanks for your help. I'll try this out......

 

[macropod]

Hi June1975,

If you're using Adobe's Acrobat Distiller, the attached code will print the active worksheet to PDF.

To use it, you need to:
. select Adobe Acrobat Distiller as the print driver
. use File|Print|Properties|Adobe PDF Settings, and uncheck the option for "Do not send fonts to Distiller", and
. add a reference to Acrobat Distiller in the VBE, via Tools|References.

Sub Print2PDF()
Dim oSheet As Worksheet
Dim oPDF As PdfDistiller
Dim TmpPSFile As String
Dim PDFFile As String
Set oSheet = ActiveSheet
Set oPDF = New PdfDistiller
TmpPSFile = "c:\TmpPSFile.ps"
PDFFile = "c:\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & ActiveSheet.Name & ".pdf"
oSheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, _
collate:=True, PrToFileName:=TmpPSFile
oPDF.FileToPDF TmpPSFile, PDFFile, ""
Kill TmpPSFile
End Sub

Cheers

[MS MVP - Word]

 

[June1975]

Thanks,
How can i get the PDF to print in landscape rather than portrait. All my pDF prints are coming out in portrait!

Also the whole print process is quite memory intensive. At the moment I am adding 7 seconds for the print job to spool and print however when I print more that 2 pages worth on a worksheet I get an Automation error - its usually when I am printing more that 1 page from a big worksheet.....

runtime error "462 : Remote Server machine does not exist

 

[KenPuls]

That setting should not be controlled by the print driver, but rather the pagesetup. You may want to take a look at your page setup manually, to see if it is indeed portrait, and then review your code to see if you toggle the settings there.

Also, you still haven't said what program you actually are using to print to the pdf.

Ken Puls, CMA - Microsoft MVP (Excel)

http://www.excelguru.ca

 

[June1975]

Hi Ken

I am using the code you gave me in this trail.
Im using Excel Worksheets to print to PDF - using VBA Excel
to automate the whole process.

Which pagesetups should I look at. Ive looked at the print settings from within Excel/ Acrobat Distiller 5 and Acrobat 5 and they all look as if they have Landscape settings.

 

[KenPuls]

Sorry, still don't follow. Are you using the code from my site (using PDF Creator) or the code from this thread (using Distiller, posted by macropod)?

If you're using the PDFCreator code, then Adobe settings won't make any difference. If you are using Adobe, I'm not sure if Adobe's Distiller has an override or not, it may.

Regardless, the settings should be controlled in Excel's page setup, as I said before.

To check your page setup in Excel, go to File|PageSetup. Is it portrait or landscape there?

Ken Puls, CMA - Microsoft MVP (Excel)

http://www.excelguru.ca

 

[moniquenguyen]

Hi I am trying to print the excel sheets to pdf format I got the problem when I looping through the sheets and print each sheet to pdf. Then the function FileToPDF fail. I can not figure why it happen. If anyone have run to this problem before please help. Thanks in advance

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName,

 

[jsch073]

Hi, I have a similar question. I am using the PDFCreator code from your website and can print and combine multiple sheets. That part is working, however the page settings in excel are not kept. Sometimes I get two pages where I normally only have one (when printing on paper). I tested this outside VBA and found if I use the PDFcreator directly in the print options it changes my page settup. What can I do about this? (I am not using the same margins on all pages)

All I need is the PDFs to look exactly as the printed pages.

Cheers

 

[macropod]

Hi jsch073,

Page layout settings in Windows apps are dependent on the drivers being used. When you switch between your printer driver and the postscript driver invoked by the PDFCreator code (or the code I posted), Excel's interpretation of what will fit on a given page is liable to change. Same goes for other Windows apps.

If you need to ensure the PDFs look the same as the printed pages, convert to PDF first, then print from the PDF, since the PDF page layout will use the same drives & page settings for both.

Cheers

[MS MVP - Word]

 

[moniquenguyen]

Hi,

I found out I don't have Acrobat Distiller printer install in my computer. There is only PDF Converter printer. Is that will affect my code if I am using the myPDF.FiletoPDF in Distiller. Thanks again.