Problem with VBA macro to create PDF from Excel

[greg72nova]

I am very much a newbie on VBA macros. I had one we obtained from Mr. Excel and used to print an Excel worksheet to PDF. But over time I have moved from Acrobat 6 to Acrobat 8 professional and from Excel 2002 to Excel 2003. Oh, to make things more fun, I control the printing by running all of this from SAS (my true love). Now when I try run the same programming, this VBA macro fails at Dim mypdfDist As New PdfDistiller and says there is no project or library available. I have made sure that the VBE Tools|References| includes the appropriate references. I do recall in Acrobat 6 that it created a printer icon, in addition to Adobe PDF called Distiller something, perhaps even PdfDistiller. In acrobat 8 there is no such printer available. Based on my best guess, I'm assuming the creation of the PDF file is a 2 step process, step 1 creating the .ps file and step 2 converting .ps to .pdf. Not sure if the two step process is unnecessary in Acrobat 8. I can open up Acrobat distiller but it's a separate program, and not a printer. I'm missing something here, or the code change is simple. Any thoughts?
Thanks.

Sub LegisreportPDF()
'STEP1: create one set of filenames
Dim temPDFfilename As String
Dim temPSfilename As String
Dim temlogfilename As String
temPSfilename = Worksheets("Sheet1").Range("D1") & ".ps"
temPDFfilename = Worksheets("Sheet1").Range("D1") & ".pdf"
temlogfilename = Worksheets("Sheet1").Range("D1") & ".log"
 
Sheets("Card").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF on Ne02:", _
printtoFile:=True, Collate:=True, Prtofilename:=temPSfilename
Dim mypdfDist As New PdfDistiller
mypdfDist.FileToPDF temPSfilename, temPDFfilename, ""
Kill temPSfilename
Kill temlogfilename
'Code came from http://www.mrexcel.com/board2//view...ghlight=&sid=9c7c018e35c106b04fc901810ebceabd
End Sub

 

[greg72nova]

Thanks to others who had similar issues and a check of issues replated to PDFMaker.xla I did work our a resolution. So others may avoid the same pitfalls, here's my code. Thanks to those of struggled with this for your insight! Just a note, I use SAS to create the path information for saving the resulting PDF and that string is located in cell D1 of Sheet1. Contact me for info if you want to know more about how this works in SAS.
Again, thanks.

Sub LegisreportPDF()
'STEP1: create one set of filenames
Dim temPDFfilename As String
Dim temPSfilename As String
Dim temlogfilename As String
Dim temPDFRawfilename As String
temPSfilename = Worksheets("Sheet1").Range("D1") & ".ps"
temPDFfilename = Worksheets("Sheet1").Range("D1") & ".pdf"
temlogfilename = Worksheets("Sheet1").Range("D1") & ".log"
Sheets("Card").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF on Ne02:", _
printtoFile:=True, Collate:=True, Prtofilename:=temPSfilename
Dim mypdfDist As New PdfDistiller
mypdfDist.FileToPDF temPSfilename, temPDFfilename, ""
Kill temPSfilename
Kill temlogfilename
End Sub

 

[Hlatigo]

Hi there...i am trying to email a few pages first into a PDF file and then into email. But first, would just like to create a pdf file.


any ideas