Problem with VBA macro to create PDF from Excel

greg72nova

New Member
Joined
Dec 10, 2008
Messages
2
:confused: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.

Rich (BB code):
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//viewtopic.php?t=200960&highlight=&sid=9c7c018e35c106b04fc901810ebceabd
End Sub
 

greg72nova

New Member
Joined
Dec 10, 2008
Messages
2
:biggrin: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.

Code:
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

Well-known Member
Joined
Jun 3, 2002
Messages
677
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
 

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top