Automation of creating PDF files

Sorry about the formatting of the code don't use these very often.

But, yes i do have the full version of acrobat.

I did set the reference to Acrobat Distiller, and it appears different people are getting different results. I will try your exact code in another file and see if I get a different result, but it looks the same.

When I run it it creates a pdf file in my y:\drive when it does executes the printout line of code and then does nothing on the call apdist....function, and my co-worker gets the following error on the call apdist line

Method 'FiletoPDF' of object 'IPDFDistiller' failed

ok your code did work so it must just be the way we have it setup....can you see anything in our code that would cause the issue?

I got it to work i think there was something wrong in our printout statement or something, because I just copied what you had and it worked finally!! Thanks!!

Just found this code and before I continue thanks for a great bit of code, we are using the code but have discovered a problem which we can't get our heads around! I am using Excel 2007 and have used the attached code, my problem is that when I run it the code goes into my excel drop down box, selects the first name then pdf's the data, however when it loops around to the next item on the list it generates the .ps file but no pdf is created, it then moves on to the 3rd drop down name and correctly pdf's it!! Really odd as it basically pdf's every other name in the drop down but annoyingly it select every item but only generates every other one!

Any help would be gugely appreciated as it is taking twice as long to run;

Sub ChangeMASelection()
Dim Rng As Range
Dim c As Range
Dim InputPSFileName As String
Dim OutPutPDFFileName As String
Dim sJobOptions As String
Dim appDist As cACroDist
Dim StrAgent, StrPath As String
' Change reference to cell containing validation to suit
With Range("DropDownList")
Set Rng = Range(Mid(.Validation.Formula1, 2, 255))
For Each c In Rng
.Value = c.Value
StrAgent = Range("DropDownList") 'get the managing agent name
StrPath = ThisWorkbook.Path

Set appDist = New cACroDist
Sheets("Charts").Select
InputPSFileName = "\\lnscntfs02\fpddata\Claims\Analysis Services Regular Report Production Files\ECF\ECF_Dashboard_Final.ps"
OutPutPDFFileName = StrPath & "\" & StrAgent & "_ECF Dashboard Report.pdf"
'OutPutPDFFileName = "\\lnscntfs02\fpddata\Claims\Analysis Services Regular Report Outputs\ECF_CTP Dashboards\2011 ECF\2011 02\ECF Dashboards\" & StrAgent & "_ECF Dashboard Report.pdf"
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", PrintToFile:=True, Collate:=True, PrToFileName:="\\lnscntfs02\fpddata\Claims\Analysis Services Regular Report Production Files\ECF\ECF_Dashboard_Final.ps"
Application.Wait Now + TimeValue("00:00:55")
Call appDist.odist.FileToPDF(InputPSFileName, OutPutPDFFileName, sJobOptions)
On Error Resume Next
' Application.Wait Now + TimeValue("00:00:05")
Kill InputPSFileName

Next c
End With
End Sub