Converting Excel worksheet to PDF then print

Discussion in 'Microsoft Excel Programming' started by kidkarma, Feb 27, 2007.

  1. kidkarma

    kidkarma Guest

    As subject suggests been tring to convert an excel sheet(with data
    validation) to convert to pdf and print each time data changes on the


    1)Method 'odist' of object 'cACroDist' failed.

    If i step through the code though it continues and appears to work
    except for

    2)the fact that althougha box appears that shows that it is 'now
    printing page 1 of 1', when i go to a printer nothing has actually

    Can anyone help me figure this. it's been killing me all day. Is there
    another alternative? I';ve looked on the web but the examples seem
    more complicated than what i need as I am only dealing with one
    worksheet rather than trying to print out sheets in many workbooks

    sorry if this hasn't made sense. Please let me know

    Sub Print2PDF()

    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
    Dim sPDFVersionAndPort As String 'Version of Adobe
    Dim appDist As cACroDist

    Set appDist = New cACroDist
    sCurrentPrinter = Application.ActivePrinter 'Save the currently
    active printer
    sPDFVersionAndPort = "Adobe PDF on Ne01:"
    sPSFileName = ThisWorkbook.Path & "\MacroData validation" & ".ps"
    'Name of PS file
    sPDFFileName = ThisWorkbook.Path & "\MacroData validation" &
    ".pdf" 'Name of PDF
    ThisWorkbook.Sheets.PrintOut ActivePrinter:=sPDFVersionAndPort, _
    PrintToFile:=True, PrToFileName:=sPSFileName 'Prints to PS

    Call appDist.odist.FileToPDF(sPSFileName, sPDFFileName,
    'Creates PDF

    Kill sPSFileName 'Removes PS

    Application.ActivePrinter = sCurrentPrinter 'Change back to the
    original printer

    End Sub

    ****BELOW is a class module named cACroDist that is also included

    Option Explicit

    Public WithEvents odist As PdfDistiller

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

    Thanks in advance!
    kidkarma, Feb 27, 2007
    1. Advertisements

  2. kidkarma

    NickHK Guest

    You code works for me as written.

    Check that appDist is actually being created properly.
    Do you get the .ps file created correctly ?

    NickHK, Feb 27, 2007
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.