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.

 

[Michael M]

Hi Dom
I'm still having problems with the code
It gets to the

Kill tempPSFileName
Kill tempLogFileName

and errors that there is no file to delete.
and also gives a print error.

Any ideas !!

 

[Domski]

Thanks Domski

I thought that was the case - will work on it see what I have done age is a terrible thing. Domski I am trying to actually get this to work from the worksheet names. I have a template that is recreated using employee names and names each worksheet based on name of employees. What I want to do is get this macro to use the worksheet name instead of the cell reference (yes I probably could just hide the cell referenced in the sheet). Just wondered if you have been able to do it based on worksheet name. I cant use the array mechanism in the thread as the names can change with each day and also from team to team. Any suggestions mate.

cheers and thanks heaps.
Ron

Hi Ron,

Just to be clear do you want to print each worksheet in the workbook to a separate pdf file then with the filename being the same as the worksheet name?

Dom

 

[Michael M]

Hi Dom
Further to my error.....
I've finally got it to "print" to file, but when I try to open the .PDF file it says " the format is not supported and can't be opened"

Any ideas / suggestions.

 

[Domski]

Hi Michael,

At home at the moment but if you post the code you've got now or upload a sample file to somewhere like Box.net or GoogleDocs I'll have a look at work tomorrow.

Dom

 

[Michael M]

Thanks mate
My initial intention was to lock the spreadsheet and E-Mail the workbook, which I have created a macro for that works.
My client now wants it converted to PDF and then E mailed......
I've had a look at Ron De Bruins site but can't make his code work for me, so I'm trying to get yours to help me out.

Sub Create_PDF()
'Dom Hill, June 2008
'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
'define file name and path in subdirectory of file where code workbook
'is stored called 'Saved Files' and picks file name up from a cell
'named as a named range call 'File_Name'
tempPDFRawFileName = "G:\Temp\road report"
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required
Sheets("Input and Map").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, PrToFileName:=tempPDFFileName
Dim mypdfDist As New PdfDistiller
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, tempPDFRawFileName, ""
'Kill tempPDFFileName
'Kill tempLogFileName
End Sub

I don't have any expectations here, and I know you've said you're not a guru, but any ideas / input would be appreciated.
PS. I have made all the necessary Reference and Font changes.

 

[kakan]

Domski
Yes you are correct in that assumption. I have spreadhseet template it recreates and names each sheet with employee name and number.
Worksheet 1 = Joe Blow 955770
Worksheet 2 = John Smith 955771
Worksheet 3 = Roy Rogers 955772

Need it to print/save to PDF

eg
Joe Blow 955770.pdf
John Smith 955771.pdf
Roy Rogers 955772.pdf

Currently what happens is I get presented with a save as window that takes the workbook name each time. yes could retype each worksheet name into the save as file name, but the operators would not be able to remember or see the worksheet names with the message window open.
So is there a way to do this at all from Excel and VBA? Or am I chasing ghosts in a fog.
Thanks for your patience - I have been searching everywhere for a solution.

Ron

 

[Michael M]

Hi Ron
Have you tried using the sheet names in an Array

Sheets(Array("Joe Blow 955770", "John Smith 955771", "Roy Rogers 955772"))

at your print output line

 

[kakan]

I did try but this list will be different for differnet operators. I though of possibly some how sending the worksheet names to a hidden sheet and picking up from there and placing in an array, but then I am getting into even deeper water. For some reason I started with a few simple VBA and Outlook forms and the boss reckons i am the excel expert. I am far from that when I look around these forums.

I have got the file to save sheets to one file now problems, but according to the boss i need to give him separate files from the proposed 200 operators.

I should point out also that we use Excel 2003 (Office) plus Adobe 7 (but I have 9 extended, lucky boy).
Guys thanks for your help it is greatly appreciated, a problem shared is often a problem solved for many.

Ron

 

[Michael M]

I'm as blind as you here, but if there aren't too many sheets could you maybe use an Input Box to ask the user who they want to report on ??

But, like you said.......this gets deeper and deeper....and over our heads !!!

 

[Domski]

Ron,

Try this:

Sub Create_PDF()
 
'Dom Hill, June 2008
'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
 
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim wsEachSheet As Worksheet
Dim mypdfDist As New PdfDistiller
 
'define file name and path in subdirectory of file where code workbook
'is stored called 'Saved Files' and picks file name up from a cell
'named as a named range call 'File_Name'

For Each wsEachSheet In ThisWorkbook.Worksheets

    tempPDFRawFileName = ThisWorkbook.Path & "\Saved Files\" & wsEachSheet.Name
 
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    tempLogFileName = tempPDFRawFileName & ".log"
 
'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required
 
    wsEachSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
        
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
    Kill tempPSFileName
    Kill tempLogFileName
    
Next wsEachSheet

Set mypdfDist = Nothing

End Sub

It will print each worksheet in the workbook to a pdf file with the name of the worksheet and save it to a sub directory called Saved Files in folder containing the workbook.

Dom