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.

 

[jimmy2timez]

--------- DOMSKI -------

New task same type of issue.

I have a workbook that is open that has multiple charts (sheets) in it. I would like to export all the charts in the active workbook to 1 single pdf file. Any ideas?

 

[Askol]

Hey, I'm trying to use this code in order to open up all of the files in a folder, and save them all to another.

However, I'm at a loss as to why this isn't working. I'm able to run it perfectly fine on my personal workbook, and it saves it just as I'd like. I then copy/pasted the code into another workbook (located in the same folder as the personal wb), and it won't work for this one. It runs through everything fine, but then no .pdf file is created in my "PDF Files" folder. It's baffling, because it is the exact same code located in the same folder, and one works, but the other doesn't.

It appears as though for some reason, when it creates the .ps file, there is some type of error, because the file size is 0kb. Then when it runs the distiller, the file can't be created. However, I have no clue why it would be able to create it for one file but not another.

Any help would be greatly appreciated!

Thanks,
Andrew

 

[Michael M]

Hi Andrew
I recently had the same problem with a workbook close event that simply would not work, even thought the copied workbook was identical !!
The only solution I could find was to delete the copied file and start again.
I also changed the file name of the copied file.
Otherwise, I'd suggest posting your code to see if there has been an error in the copy and paste process.

 

[Askol]

Pretty sure there isn't a problem with the code, as I have correctly run this on a number of other files.

What I will be using this for is taking hundreds of "Time Sheets", and converting them to PDFs. The "Time Sheets" are all the same file template from each person, and for some reason the template won't convert to a .ps file. The only possible reason I can think of is there are custom colors, but I don't know why that would matter.

In case it could help, here's the code:

Thanks!

Sub Create_PDF3()
 
'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'
wbName = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".", vbTextCompare) - 1)
tempPDFRawFileName = ThisWorkbook.Path & "\Print Jobs\" & wbName
'TargetFolder = Range("a1").Value
'Workbooks.Open FileName:=TargetFolder & Dir(TargetFolder & "*.xls"), UpdateLinks:=0
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(1).PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
 
Dim mypdfDist As New PdfDistiller
 
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
'Kill tempPSFileName
Kill tempLogFileName
 
End Sub

 

[kakan]

One of the problems I have had in the past was copying files to another machine. I am sure you said somewhere that your work machine works but not on another.

Check the >tools>references and make sure Adobe Distiller is checked.

Earlier in this thread is mention of this. Just a thought as you mentioned it wont create the PS file.

cheers

Ron

 

[Temporary-Failure]

Re: Save Excel Sheet As PDF File (NON SOLVED)

I have following code. It's very unreliable. The porblem is that it works only one or two times. Then it doesn't make pdf anymore. Why? It's annoying also that every time when I open workbook I have to manually deselect the Do not send fonts to "Adobe PDF" option. Is it possible to make automatically? There's no reason to use this macro if these problems occures every time.

Private Sub CommandButton2_Click()

'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 = "E:\Copiot O - V\Omat tiedostot\Yritys\Laskut ja kuitit\Laskut\" & ActiveSheet.Range("H6").Value
 
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("LASKU").PrintOut Copies:=1, From:=1, To:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
 
Dim mypdfDist As New PdfDistiller
 
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
Kill tempPSFileName
Kill tempLogFileName

 

[Temporary-Failure]

Is it possible to uncheck "Don't send fonts to Adobe PDF" with macro?

 

[Domski]

Hi,

I've not come across that issue myself.

Dom

 

[KREdR]

I'm having an issue where the code is not picking up the file name I assign. See the code below:

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim myPDFDist As New PdfDistiller

    Range("B3").Select
    ptname = ActiveCell  '   curco & curyr & curver
        

tempPDFRawFileName = "C:\Users\kreed\Desktop\" & Range("B3").Value
    
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
Calculate
    Call HideRows
'----------------------------------------------------
'print out the cash flow
    Worksheets("PM").Activate
    ActiveSheet.PageSetup.PrintArea = "a_CFlow_PrintArea"  'ActiveSheet.PageSetup.PrintArea = "a_CFlow_PrintArea"
    ptnamCF = ptname & " CF"
    Filename = FilePath & ptnamCF & ".ps"   

    Columns("V:AH").EntireColumn.AutoFit
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:= _
    False, ActivePrinter:="Adobe PDF", PrintToFile:= _
    True, Collate:=True, PrToFilename:=tempPSFileName
    
    myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow
    
    Kill tempPSFileName
    Kill tempLogFileName
[CODE]

It should be adding the " CF" to the active cell selection when printing but it is not.  This is creating a issue because the macro repeats itself (not included) to print other named Print Areas and should create a new printed PDF for each section.  Currently, it is overwriting the last printed section with a new file each time.  Any thoughts?