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.

 

[Domski]

Michael,

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 mypdfDist As New PdfDistiller
 
tempPDFRawFileName = "G:\Temp\road report"
 
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
 
Sheets("Input and Map").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
    printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
        
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
Kill tempPSFileName
Kill tempLogFileName
    
Set mypdfDist = Nothing

End Sub

Dom

 

[Michael M]

Hi Dom
This is **** close.
It saves one of the 2 sheets to G:\Temp but the second one appears as a .ps file.
I'm wondering if the problem is because that sheet contains a couple of hundred "FreeForm" shapes, they are lines on a map.
I adapted some of the code you sent Ron...path name and sheets lines

Sub NuCreate_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 mypdfDist As New PdfDistiller
 
For Each wsEachSheet In ThisWorkbook.Worksheets

    tempPDFRawFileName = "G:\Temp\" & 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

 

[Michael M]

Dom
Found the problem.
I logged off last night and we have the most paranoid network in the world, well, my world anyway.
Our system, Adobe and Excel, etc, have default settings that revert at each logon.
I have change the system fonts again and it works a treat.

All I need to do now is work out how to E-Mail the PDF to a group of people on a list.

As for you, my friend, you are not a VBA newbie, you are, in my opinion well on your way to expert.....I dips me lid to you sir.

 

[Domski]

Glad you got it working.

Ron de Bruin has loads of good example of sending mail with VBA on his site here.

Hope it helps,

Dom

 

[Michael M]

Thanks Dom
I have visited Rons site but cannot get anything to work as it should.
I suspect a couple of lines in one of the functions is causing some issues and have made a request to our IT dept to investigate.
Anything in a function or macro that points to a "C:\" is poison to our system and won't work.

 

[kakan]

Sorry Dom

I only just got back to this issue, others arose. mate the **** system is teasing me now.
It looked so promising and thanks for the work. It went of and thought about doing as it was requested. It displayed a print message for a brief second.

Then bombs out. debug takes me to the following code.

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

Any ideas. I have gone back over the forum and double checked all of the references to ensure adobe distiller has been selected along with my make sure all adobe items are selected.

I still get the above.

cheers

Ron

 

[Domski]

Thanks Dom
I have visited Rons site but cannot get anything to work as it should.
I suspect a couple of lines in one of the functions is causing some issues and have made a request to our IT dept to investigate.
Anything in a function or macro that points to a "C:\" is poison to our system and won't work.

You can change that to point at any drive you want to. Start another thread and PM me the details and I'll have a butchers.

Dom

 

[Domski]

Sorry Dom

I only just got back to this issue, others arose. mate the **** system is teasing me now.
It looked so promising and thanks for the work. It went of and thought about doing as it was requested. It displayed a print message for a brief second.

Then bombs out. debug takes me to the following code.

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

Any ideas. I have gone back over the forum and double checked all of the references to ensure adobe distiller has been selected along with my make sure all adobe items are selected.

I still get the above.

cheers

Ron

Can you upload a sample workbook with the code that isn't working for you to Box.net/GoogleDocs or somewhere similar. I'll check it out at work where I've got Adobe installed.

Dom

 

[Domski]

Ron,

Your problem was hidden worksheets. This should take care of that and also avoid printing a few other sheets that I'm assuming you won't want to print each time:

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

    If wsEachSheet.Visible = xlSheetVisible And wsEachSheet.Name <> "Instructions." _
        And wsEachSheet.Name <> "Employee_Creation" And wsEachSheet.Name <> "Attendance table" Then
    
        tempPDFRawFileName = ThisWorkbook.Path & "\Saved Files\" & wsEachSheet.Name
 
        tempPSFileName = tempPDFRawFileName & ".ps"
        tempPDFFileName = tempPDFRawFileName & ".pdf"
        tempLogFileName = tempPDFRawFileName & ".log"
 
        wsEachSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
            printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
        
        mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
        Kill tempPSFileName
        Kill tempLogFileName
    
    End If
    
Next wsEachSheet

Set mypdfDist = Nothing

End Sub

You'd also enabled a lot of libraries that I don't have with my version of Adobe but doubt they would cause a problem. The only one I need to have enabled to get the code to work with Acrobat Pro 6.0 is Acrobat Distiller.

Hope it helps,

Dom

 

[kakan]

Hi Dom

You are not the guru I thought you were - Mate you are the Great Grand Master. This problem has been with me for sometime since the inception of the project. You have saved me from drinking too much It works a treat. Thank you very much.
Be assured you are preserved in code comments for the life of this form.

Yes the invisible command thank you for teaching something. The hidden sheets were actually only there while I work on my improved input form, maybe more drinking there the way things are going.


Great work oh Grand Master Domski.