Converting Excel worksheet to PDF then print

[kidkarma]

Hi,
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
sheet.

ISSUES:

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
printed


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,
sJobOptions)
'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!

 

[NickHK]

You code works for me as written.

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

NickHK