Thread: Automation of creating PDF files

We are trying to automate the creation of PDF files for each sheet in an Excel file,a nd keep getting error messages. Can someone help? It produces the post script file, but it has no data nd then doesn't create a PDf file at all. We have Excel 2003, and have the below code setup.

Set appDist = New cAcroDist
            Sheets("CAD Roll").Select
            InputPSFileName = "\\nanflp03\cola\123g\coladmin\Dreamweaver\New Albany CMA\Reports\Today\RollCAD.ps"
            OutPutPDFFileName = "\\nanflp03\cola\123g\coladmin\Dreamweaver\New Albany CMA\Reports\Today\RollCAD.pdf"
            Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
            "Acrobat PDFWriter on LPT1:", PrintToFile:=True, Collate:=True, PrToFileName:="\\nanflp03\cola\123g\coladmin\Dreamweaver\New Albany CMA\Reports\Today\RollCAD.ps"
            Call appDist.odist.FileToPDF(InputPSFileName, OutPutPDFFileName, sjobOptions)
            Kill InputPSFileName

We also have a class module setup called cAcroDist with the below code.

Option Explicit

Public WithEvents odist As PdfDistiller

Private Sub Class_Initialize()
Set odist = New PdfDistiller
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

You do have Acrobat and not just the reader?

You need to add a Class Module & name it - cACroDist. Paste this code

Option Explicit
Public WithEvents odist As PdfDistiller
 
Private Sub Class_Initialize()
    Set odist = New PdfDistiller
End Sub

In a Standard Module:

Sub PrintWholeWorksheetToPDF()
'change file name & path to match your needs
    Dim sPSFileName As String    'Name of PS to be created
    Dim sPDFFileName As String    'Name of PDF to be created
    Dim sJobOptions As String
    Dim sCurrentPrinter As String    'Same current printer choice to resume at end
    Const sPDFVersionAndPort As String = "Adobe PDF on Ne07:"
    Dim appDist As cAcroDist
    On Error GoTo exit_handler
    Set appDist = New cAcroDist
    sCurrentPrinter = Application.ActivePrinter    'Save the currently active printer
    sPSFileName = ThisWorkbook.Path & "\MyFileName.ps"  'Name of PS file
    sPDFFileName = ThisWorkbook.Path & "\MyFileName" & ".pdf"    'Name of PDF
    ThisWorkbook.Sheets.PrintOut ActivePrinter:=sPDFVersionAndPort, _
                                 PrintToFile:=True, PrToFileName:=sPSFileName    'Prints to PS

    'Creates PDF
    Call appDist.odist.FileToPDF(sPSFileName, sPDFFileName, sJobOptions)
    Kill sPSFileName    'Removes PS
    Application.ActivePrinter = sCurrentPrinter    'Change back to the original printer
    MsgBox "Done", vbInformation, "PDF Created"
    Exit Sub
exit_handler:
    MsgBox "Could not create PDF", vbCritical, "Error occurred"

End Sub

Finally you need to set a Reference to Acrobat Distiller in the VB Editor

Tools -. References

That is what I have, I believe...as you can see above I did include the cAcroDist class module, and I believe my code is the same except for the error handling (which i didn't include), and the dim statements, which i do have I just didn't copy them above. This is exactly what I have entirely in my code.

Public Sub CommandButton2_Click()
Dim InputPSFileName As String
Dim OutPutPDFFileName As String
Dim sJobOptions As String
Dim appDist As cAcroDist

Msg = "Are you sure you want to PDF the SkipCADRoll Reports?"
    Style = vbYesNo + vbQuestion
    Title = "Create PDFs?"
    Response = MsgBox(Msg, Style, Title)
    
        If Response = vbYes Then
            Set appDist = New cAcroDist
            Sheets("CAD Roll").Select
            InputPSFileName = "\\nanflp03\cola\123g\coladmin\Dreamweaver\New Albany CMA\Reports\Today\RollCAD.ps"
            OutPutPDFFileName = "\\nanflp03\cola\123g\coladmin\Dreamweaver\New Albany CMA\Reports\Today\RollCAD.pdf"
            Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
            "Acrobat PDFWriter on LPT1:", PrintToFile:=True, Collate:=True, PrToFileName:="\\nanflp03\cola\123g\coladmin\Dreamweaver\New Albany CMA\Reports\Today\RollCAD.ps"
            Call appDist.odist.FileToPDF(InputPSFileName, OutPutPDFFileName, sJobOptions)
            Kill InputPSFileName
           
            Msg = "All Acrobat Drop Files have been created successfully."
            Style = vbOKOnly + vbInformation
            Title = "PDF Files Successful"
            Response = MsgBox(Msg, Style, Title)
            
            Cells(15, 3).Value = DateInsert
            Application.CutCopyMode = False
            
                        
        Else
        
            Msg = "The report will not be PDFed."
            Style = vbOKOnly + vbExclamation
            Title = "PDF Cancelled"
            Response = MsgBox(Msg, Style, Title)
            Cells(10, 8).Value = "Aborted " + DateInsert
            Application.CutCopyMode = False
            
        End If

End Sub

And My CAcroDist class module is as follows

Option Explicit

Public WithEvents odist As PdfDistiller

Private Sub Class_Initialize()
    Set odist = New PdfDistiller
End Sub

I did already have the Acrobat Distiller reference selected, but it still will not work....I just re-ran it and it looks like it actually created the PDF file in my y:\ drive (which is our personal drive) instead of the location specified in the OutPutPDFFileName in the code. What would cause this? is it a default setting perhaps on my Acrobat?

Please use Code tags.

Have you tried my code, it certainly works for me.

You must make the Reference to Acrobat Distiller as i said in my previous post

maybe the printer name is incorrect in your code.