VBA, Excel, Printing to PDF

I would like to automate the process of printing a excel worksheet to a pdf with a certain naming convention and file name.  Currently I'm using the following code.  However, when I run the code a .log file is created that says pdf creation failed.  Please help.


Sub PDF()
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "S:\Accounting\03 IconBurger(SmashBurger)\myPostScript.ps"
PDFFileName = "S:\Accounting\03 IconBurger(SmashBurger)\test.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
MySheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
End Sub

Open in new window

Tbyrd777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nike_golfCommented:
A couple of things to check first.

Are you using the the full version of Acrobat that includes Distiller?

Do you have a reference to Distiller in your source code?

Have you changed the properties for the PDF printer to not send Fonts?   (http://oscl.com/kc/14818.htm)

NG,
0
nike_golfCommented:
0
Tbyrd777Author Commented:
Nike_golf - I'm not sure if I'm using the full version of Acrobat that includes Distiller.  Is there a way to check.  I know that I have the Acrobat Distiller 8 installed on my computer along with Adobe Acrobat 8 Standard.  When I go into the printer properties I don't have an option for " Do not Send Fonds to 'Adobe PDF'".  I'm not sure if I have a reference to to the Distiller in my source code.  I copied the code from a website.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tbyrd777Author Commented:
Actually, I just realized what you're referring to as a reference and YES I do have the reference selected for Acrobat Distiller.
0
Tbyrd777Author Commented:
I looked at the question you referenced that uses a public function called PrintSheetsToPDF however, I seem to locate in the answer the actual code for that function.
0
nike_golfCommented:
If you have Distiller installed that is all that is required. do you have an "Adobe PDF" printer driver for printers installed?

Open the VB Editor and goto Tool/References/  set a reference to Acrobat distiller.

The fonts checkbox on my box actually says, "rely on system fonts only; do not use document fonts" uncheck this (see image).


NG,


untitled.JPG
0
Tbyrd777Author Commented:
IAdobe PDF printer is installed and I checked the box "rely on system fonts only; do not use document fonts" . Also, the Adobe Distiller reference is selected in VBA. However, when I run the macro I still do not get a pdf file.  I get a log file that says
%%[ Error: undefined; OffendingCommand: E*t600R&u600D*r0F*o4W ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%
0
nike_golfCommented:
You want to uncheck to the "rely on system fonts only; do not use document fonts" box.

Can you print to pdf from any other application?

NG,
0
Tbyrd777Author Commented:
Okay, this box is unchecked.  Also, yes I don't have any problem printing the worksheet to pdf manually.

I tried the code from the previous question you referenced and now I'm getting an Error saying I need to uncheck the 'Do not send fonts to "Adobe PDF"" I've already unchecked the corresonding box and restarted excel.  Also, the same problem still exists when I run the original code even with the box unchecked.  I'm attached a print screen of my ADP Printer set-up as well as the error I get when I run the attached code.
Public Function PrintSheetsToPDF( _
      ByVal SheetsToPrint As Variant, _
      ByVal PDFFilePath As String, _
      Optional ByVal ReorderSheets As Boolean _
   ) As Boolean

' Print the specified sheets to a PDF file in the order specified. Requires
' Adobe Acrobat 7.0 and a reference to Acrobat Distiller. Returns True if the
' print was successful, False otherwise.
'
' Syntax
'
' PrintSheetsToPDF(Sheets, PDFFilePath, [ReorderSheets])
'
' SheetsToPrint - Array of sheet names to be printed. The sheets included are
'   sorted in that order and then printed in one print job. When the printing
'   is complete the original order is restored.
'
' PDFFilePath - Full path to the PDF file.
'
' ReorderSheets - Pass True to reorder the sheets to be printed in the order
'   specified, False to not sort. optional. If omitted then False is assumed.
'
' Example
'
' Print sheets "Sheet4", "Sheet10", and "Sheet1" in that order:
'   PrintSheetsToPDF Array("Sheet4", "Sheet10", "Sheet1"), "C:\Output.PDF"
   
   Dim Errors As Boolean
   Dim OriginalActiveWorksheet As Object
   Dim OriginalOrderNames As Variant
   Dim Index As Long
   Dim PDFDistillerApplication As PdfDistiller
   Dim TempPFFilePathName As String
   Dim PDFLogPathName As String
   Dim Result As Long
   
   ' Normalize the sheets to print parameter
   If Not IsArray(SheetsToPrint) Then SheetsToPrint = Array(SheetsToPrint)
   For Index = LBound(SheetsToPrint) To UBound(SheetsToPrint)
      If TypeName(SheetsToPrint(Index)) = "Worksheet" Then SheetsToPrint(Index) = SheetsToPrint(Index).Name
   Next Index
   
   ' Normalize the output pdf file name
   If LCase(Right(PDFFilePath, 4)) <> ".pdf" Then PDFFilePath = PDFFilePath & ".pdf"
   
   ' Save the current active worksheet
   Set OriginalActiveWorksheet = ActiveSheet
   
   If ReorderSheets Then
   
      ' Save the current sheet order
      ReDim OriginalOrderNames(1 To ThisWorkbook.Sheets.Count)
      For Index = 1 To ThisWorkbook.Sheets.Count
         OriginalOrderNames(Index) = ThisWorkbook.Sheets(Index).Name
      Next Index
      
      ' Reorder the worksheets
      For Index = UBound(SheetsToPrint) To LBound(SheetsToPrint) Step -1
         If ThisWorkbook.Sheets(SheetsToPrint(Index)).Index > 1 Then
            ThisWorkbook.Sheets(SheetsToPrint(Index)).Move Before:=ThisWorkbook.Sheets(1)
         End If
      Next Index
   
   End If
   
   ' Print the worksheets
   
   TempPFFilePathName = Left(PDFFilePath, InStrRev(PDFFilePath, ".")) & "pf"
   PDFLogPathName = Left(PDFFilePath, InStrRev(PDFFilePath, ".")) & "log"
   On Error Resume Next
   If Dir("TempPFFilePathName") <> "" Then
     Kill TempPFFilePathName
    
    End If

 
   Err.Clear
   ThisWorkbook.Worksheets(SheetsToPrint).PrintOut ActivePrinter:="Adobe PDF", PrintToFile:=True, Collate:=True, PrToFilename:=TempPFFilePathName
   If Err.Number <> 0 Then
      MsgBox "To prevent this error from occurring in the future, open the Properties window for the 'Adobe PDF' printer, click the command button 'Printing Preferences', and uncheck the option 'Do not send fonts to ""Adobe PDF""'. Before the changes will take effect Excel must be quit and restarted."
      Errors = True
   End If
   On Error GoTo 0

   If ReorderSheets Then
      ' Restore the original worksheet order
      For Index = 1 To ThisWorkbook.Sheets.Count
         If ThisWorkbook.Sheets(OriginalOrderNames(Index)).Index <> Index Then
            ThisWorkbook.Sheets(OriginalOrderNames(Index)).Move Before:=ThisWorkbook.Sheets(Index)
         End If
      Next Index
   End If
   
   ' Restore the original active worksheet
   OriginalActiveWorksheet.Activate

   If Not Errors Then
      ' Convert the postscript file to .pdf
      Set PDFDistillerApplication = New PdfDistiller
      Result = PDFDistillerApplication.FileToPDF(TempPFFilePathName, PDFFilePath, "")
      On Error Resume Next
      Kill TempPFFilePathName
      If Result = 1 Then Kill PDFLogPathName
      On Error GoTo 0
   End If
   
   PrintSheetsToPDF = Not Errors
   
End Function


Public Sub GeneratePDF()

' Calls Public Function PrintSheetsToPDF which will
' Print the specified sheets to a PDF file in the order specified. Requires
' Adobe Acrobat 7.0 and a reference to Acrobat Distiller. Returns True if the
' print was successful, False otherwise.
'
' Syntax
'
' PrintSheetsToPDF(Sheets, PDFFilePath, [ReorderSheets])
'
' SheetsToPrint - Array of sheet names to be printed. The sheets included are
'   sorted in that order and then printed in one print job. When the printing
'   is complete the original order is restored.
'
' PDFFilePath - Full path to the PDF file.
'
' ReorderSheets - Pass True to reorder the sheets to be printed in the order
'   specified, False to not sort. optional. If omitted then False is assumed.
'
' Example
'
' Print sheets "Sheet4", "Sheet10", and "Sheet1" in that order:
'   PrintSheetsToPDF Array("Sheet4", "Sheet10", "Sheet1"), "C:\Output.PDF"



  'PrintSheetsToPDF "Sheet1", Sheets("Sheet1").[A1] & "\" & Sheets("Sheet1").[B1]
  'Note: assumes the path is in Sheet1!A1 and the file name is in Sheet1!B1.
  
  PrintSheetsToPDF "Scorecard", "S:\Accounting\03 IconBurger(SmashBurger)\test.pdf"

End Sub

Open in new window

Set-up---Errors.doc
0
nike_golfCommented:
The attachment you sent still has the "rely on system fonts only; do not use document fonts" box checked, this needs to be unchecked.

Can you attach an example of your worksheet?

NG,
0
Tbyrd777Author Commented:
Please see revised attachment as well as Excel Workbook with macros
Set-up---Errors.doc
Print-to-Adobe-Workbook.xls
0
nike_golfCommented:
You might just download the final product from the other thread (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25753162.html) I supplied and run it.

If it doesn't run correctly then your PC isn't setup correctly - that will eliminate any coding problems. The path that it uses is called C:\scorecard\ or you can change it but if you don't you will need that file folder to exist before running it.

NG,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nike_golfCommented:
Your workbook and source ran fine on my box.

NG,
0
nike_golfCommented:
note: I had to change the Print2PDF() function - pdffile directory from S:\ to a local drive though.

NG,
0
nike_golfCommented:
Are you still getting error logs from Distiller?

0
Tbyrd777Author Commented:
Yeah, this worked with the attached code. However, I'm wondering if there is a way to change the code so that the pdf file remains open so the user can insert additional documents as needed.
Public Function PrintSheetsToPDF( _ 
      ByVal SheetsToPrint As Variant, _ 
      ByVal PDFFilePath As String, _ 
      Optional ByVal ReorderSheets As Boolean _ 
   ) As Boolean 
 
' Print the specified sheets to a PDF file in the order specified. Requires 
' Adobe Acrobat 7.0 and a reference to Acrobat Distiller. Returns True if the 
' print was successful, False otherwise. 
' 
' Syntax 
' 
' PrintSheetsToPDF(Sheets, PDFFilePath, [ReorderSheets]) 
' 
' SheetsToPrint - Array of sheet names to be printed. The sheets included are 
'   sorted in that order and then printed in one print job. When the printing 
'   is complete the original order is restored. 
' 
' PDFFilePath - Full path to the PDF file. 
' 
' ReorderSheets - Pass True to reorder the sheets to be printed in the order 
'   specified, False to not sort. optional. If omitted then False is assumed. 
' 
' Example 
' 
' Print sheets "Sheet4", "Sheet10", and "Sheet1" in that order: 
'   PrintSheetsToPDF Array("Sheet4", "Sheet10", "Sheet1"), "C:\Output.PDF" 
    
   Dim Errors As Boolean 
   Dim OriginalActiveWorksheet As Object 
   Dim OriginalOrderNames As Variant 
   Dim Index As Long 
   Dim PDFDistillerApplication As PdfDistiller 
   Dim TempPFFilePathName As String 
   Dim PDFLogPathName As String 
   Dim Result As Long 
    
   ' Normalize the sheets to print parameter 
   If Not IsArray(SheetsToPrint) Then SheetsToPrint = Array(SheetsToPrint) 
   For Index = LBound(SheetsToPrint) To UBound(SheetsToPrint) 
      If TypeName(SheetsToPrint(Index)) = "Worksheet" Then SheetsToPrint(Index) = SheetsToPrint(Index).Name 
   Next Index 
    
   ' Normalize the output pdf file name 
   If LCase(Right(PDFFilePath, 4)) <> ".pdf" Then PDFFilePath = PDFFilePath & ".pdf" 
    
   ' Save the current active worksheet 
   Set OriginalActiveWorksheet = ActiveSheet 
    
   If ReorderSheets Then 
    
      ' Save the current sheet order 
      ReDim OriginalOrderNames(1 To ThisWorkbook.Sheets.Count) 
      For Index = 1 To ThisWorkbook.Sheets.Count 
         OriginalOrderNames(Index) = ThisWorkbook.Sheets(Index).Name 
      Next Index 
       
      ' Reorder the worksheets 
      For Index = UBound(SheetsToPrint) To LBound(SheetsToPrint) Step -1 
         If ThisWorkbook.Sheets(SheetsToPrint(Index)).Index > 1 Then 
            ThisWorkbook.Sheets(SheetsToPrint(Index)).Move Before:=ThisWorkbook.Sheets(1) 
         End If 
      Next Index 
    
   End If 
    
   ' Print the worksheets 
    
   TempPFFilePathName = Left(PDFFilePath, InStrRev(PDFFilePath, ".")) & "pf" 
   PDFLogPathName = Left(PDFFilePath, InStrRev(PDFFilePath, ".")) & "log" 
   On Error Resume Next 
   If Dir("TempPFFilePathName") <> "" Then 
     Kill TempPFFilePathName 
     
    End If 
 
  
   Err.Clear 
   ThisWorkbook.Worksheets(SheetsToPrint).PrintOut ActivePrinter:="Adobe PDF", PrintToFile:=True, Collate:=True, PrToFilename:=TempPFFilePathName 
   If Err.Number <> 0 Then 
      MsgBox "To prevent this error from occurring in the future, open the Properties window for the 'Adobe PDF' printer, click the command button 'Printing Preferences', and uncheck the option 'Do not send fonts to ""Adobe PDF""'. Before the changes will take effect Excel must be quit and restarted." 
      Errors = True 
   End If 
   On Error GoTo 0 
 
   If ReorderSheets Then 
      ' Restore the original worksheet order 
      For Index = 1 To ThisWorkbook.Sheets.Count 
         If ThisWorkbook.Sheets(OriginalOrderNames(Index)).Index <> Index Then 
            ThisWorkbook.Sheets(OriginalOrderNames(Index)).Move Before:=ThisWorkbook.Sheets(Index) 
         End If 
      Next Index 
   End If 
    
   ' Restore the original active worksheet 
   OriginalActiveWorksheet.Activate 
 
   If Not Errors Then 
      ' Convert the postscript file to .pdf 
      Set PDFDistillerApplication = New PdfDistiller 
      Result = PDFDistillerApplication.FileToPDF(TempPFFilePathName, PDFFilePath, "") 
      On Error Resume Next 
      Kill TempPFFilePathName 
      If Result = 1 Then Kill PDFLogPathName 
      On Error GoTo 0 
   End If 
    
   PrintSheetsToPDF = Not Errors 
    
End Function

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Acrobat

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.