Save Excel Sheet As PDF File (SOLVED)

[Domski]

Hi,

I've been working on this problem on and off for a number of months now and have just about got it sussed so thought I'd share it with you as it has mostly been down to postings on this board that I've got it in the end.

Thanks especially to 'biggoan' for his post: http://www.mrexcel.com/board2/viewtopic.php?t=199136&highlight=

Anyway, this seems to avoid the need for a class module but does need you to install the Acrobat Distiller object references in Tools...References in the VBA editor.

You also need to go into the printer properties of the your Adobe PDF 'Printer' and under Printing Preferences...Adobe PDF Settings deselect the Do not send fonts to "Adobe PDF" option. Why, who knows!

Private Sub Create_PDF()

'Created by Dom Hill with considerable asistance from Biggoan and Mr Excel

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

Sheets("Sales Data").Activate

tempPDFRawFileName = "C:\" & Range("A1").Value

'Define the postscript and .pdf file names.

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

' Print the Excel range to the postscript file

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

'Create PDF File

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow

'Delete PS File

Kill tempPSFileName
Kill tempLogFileName

End Sub

Not sure why the macro creates a log file but if you know more about VBA then you probably would.

Hope it helps someone else as it's going to save me a heap loads of time.

 

[Andy Rayner]

Hello

I am trying to use your code to convert the whole workbook into a pdf file.

here is how I have amended your code.

Private Sub CommandButton1_Click()

'Created by Dom Hill with considerable asistance from Biggoan and Mr Excel

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

'Sheets("Sales Data").Activate

tempPDFRawFileName = "H:\"

'Define the postscript and .pdf file names.

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

' Print the Excel range to the postscript file
For Each sht In Workbooks(ThisWorkbook.Name).Sheets

If (sht.Name <> "Make PDF") Then
    Sheets(sht.Name).Activate
    ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
End If


'Create PDF File

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow

Next
'Delete PS File

Kill tempPSFileName
Kill tempLogFileName


End Sub

For some reason it doesnt seem to be working and produces an error on this line

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

Can you give any insight into this?
Many thanks

Andy

 

[dennisli]

I tested your code but got one error message: “Use defined type not defined” about code: Dim myPDFDist As New PdfDistiller

Any ideas?

 

[Domski]

I tested your code but got one error message: “Use defined type not defined” about code: Dim myPDFDist As New PdfDistiller

Any ideas?

In the VB Editor I think you need to go into Tools...References and ensure Acrobat Distiller is selected.

Any joy?

 

[dennisli]

Excel to PDF

In the VB Editor I think you need to go into Tools...References and ensure Acrobat Distiller is selected.
I checked VB Editor-Tools-References and only found AcroIEhelper1.0 Type Library and selected it. But it did not work. Are you sure Acrobat Distiller is standard?
I love this code as I need it to covert my Excel sheets to PDF formats.
Thanks.
Dennis

 

[dennisli]

In the VB Editor I think you need to go into Tools...References and ensure Acrobat Distiller is selected.
I checked VB Editor-Tools-References and only found AcroIEhelper1.0 Type Library and selected it. But it did not work. Are you sure Acrobat Distiller is standard?
I love this code as I need it to covert my Excel sheets to PDF formats.
Thanks.
Dennis

 

[Domski]

To be honest I'm no code expert (i.e. complete novice). I got this working more by luck than judgment.

I have Adobe Acrobat Professional 6.0 on my PC at work I think. There's loads of Adobe libraries to choose from on it and this was the one that worked for me.

Sorry can't be much more help.

 

[Andy Rayner]

I got it working, but the problem is that it overwites the .ps file with each worksheet. so when it creates the PDF file in the end it its just one of the sheets.

I wonder if there is a way to add all the sheets to the PDF file.

 

[Domski]

Hi Andy,

One solution would be to modify the code to include the part where the file names are defined within the loop.

You could then pick up the worksheet name to use as the file name.

It would create multiple files but you could then easily pull them together in Adobe.

If you need any help post your code that you've got now and I'll have a look at it.

 

[I Maycotte]

PDF to Printer

While I created similar code to print to pdf, I am now wondering if w/in the same code I can send the newly created pdf file to the printer. If so, does anyone know how I may go about this? I appreciate the help I get. Thanks.

---
Isaac Maycotte