Converting Excel worksheet to PDF then print

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!

You code works for me as written.

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

NickHK

"kidkarma" <jack.pk.ang@gmail.com> wrote in message
news:1172556190.465742.278480@t69g2000cwt.googlegroups.com...
> 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!
>