Save Excel Sheet As PDF File (SOLVED)

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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!

Code:
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. (y)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.

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
Code:
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
 
Upvote 0
I tested your code but got one error message: “Use defined type not defined” about code: Dim myPDFDist As New PdfDistiller

Any ideas?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top