How to save the excel as pdf?

[clif]

How to save the excel as pdf? I have pdf printer and want to save the xls. as pdf. but some website teach me to save as ps file and then convert into pdf. My excel do not have required reference library! Since i want to save as a pdf file in a particular path without seeing the browser. Anyone can help me?

[Kenneth Hobs]

For Excel 2007+:
[VBA]Sub Test_PublishToPDF()
Dim sDirectoryLocation As String, sName As String

sDirectoryLocation = ThisWorkbook.Path
sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf"
PublishToPDF sName, activeworksheet
End Sub


Sub PublishToPDF(fName As String, ws As Worksheet)
Dim rc As Variant

'ChDrive "c:"
'ChDir GetFolderName(fName)
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If Not rc Then Exit Sub

ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub[/VBA]

[Paul_Hossler]

I'm not sure what a PDF printer is, nor do I understand about the PostScript or seeing the browser.

However, if Ken's ExportAsFixedFormat doesn't work for you, I've been very satisfied with the freeware


http://cutepdf.com/Products/CutePDF/writer.asp


After installed, you can use the normal Print of almost any program (not just Excel) to 'print' to the CutePDF 'printer', but it just makes a nice PDF file for you

Paul

[jaminben]

Hi,

Sorry for dragging this old post back up but I'm wondering if anyone ever got this to work?

I'm trying to auto print my excel sheets to a PDF file but get different results depending on the program I use. CutePDF seems to work the best for my needs but I'm unable to get it to save the file correctly (its corrupted) without having to alter any registry keys as suggested in other posts.

Thanks

Ben

Edit:

Here's my code so far

[vba]Sub Print_To_PDF()

Dim fileName As String
fileName = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".", -1, vbTextCompare) - 1)) & ".pdf"
Debug.Print "Filename: " & fileName
Select_Sheets_To_PDF 'this just selects the sheets I want to print to file

Application.ActivePrinter = "CutePDF Writer on CPW2:"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", PrintToFile:=True, Collate:=True, prtofilename:=fileName

End Sub[/vba]

[jolivanes]

What happens if you change

fileName = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".", -1, vbTextCompare) - 1)) & ".pdf"

to

fileName = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".", -1, vbTextCompare))) & "pdf"

[jaminben]

Thanks for the reply

Its not the file extension thats causing the issue (I did try your suggestion just to make sure) but more the way CutePDF works. I think, don't quote me on this, but somehow I need to use registry keys to setup a temp file name and once its done that it then needs to save it as a PDF... something like that anyway. Here's a link to their site which lists all the keys:

CutePDF

I've gone back to using the MS save as PDF feature as CutePDF was doing my head in but if anyone knows of a method to use CutePDF I'll give it a try as that is my prefered method (my page layout works better).

Thanks

Ben