Re: Vba printout

From: Curtis (cmarchand_at_norambar.ca)
Date: 10/07/04 

Date: 7 Oct 2004 12:46:12 -0700

cmarchand@norambar.ca (Curtis) wrote in message news:<77459c6d.0410070548.3cf7d8f2@posting.google.com>...
> cmarchand@norambar.ca (Curtis) wrote in message news:<77459c6d.0410061127.2e84b473@posting.google.com>...
> > Hi all,
> >
> > I'm using xl2000 on a win2000 with adobe pdfwriter 5.0
> > to create from a .xls file a .pdf file.
> >
> >
> > Sub Macro1()
> > Application.activate = "Acrobat PDFWriter sur LPT1:"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
> > ActivePrinter:="Acrobat PDFWriter sur LPT1:", _
> > PrintToFile:= True, PrToFileName:="C:\test.pdf"
> > End Sub
> >
> > What appens is that a file in c:\ is created but cant be viewed
> > because an error as occurred , no error number, and on desktop file
> > named test.pdf is created and viewable.
> >
> > Whats going on I dont understand. Help!
> >
> > Thanks!
> > Curtis
>
> After some tests I found out that the file that is viewable is saved
> in default directory specified in excel tools--option--general. And
> the file name/path that is specified in prtofilename is never
> viewable.
>
> I still dont have a clue on how to solve this problem.
>
> Curtis

I figured it out
Here is the solution
the printout creates a postscript file then you convert the postscript
file to a pdf file see code that follows

Sub Macro1()
   Dim myPDF As PDFDistiller
   Set myPDF = New PDFDistiller

   ' Not needed since you specify it in printout
   Application.Activate = "Acrobat PDFDistiller sur LPT1:"

   ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
      ActivePrinter:="Acrobat PDFDistiller sur LPT1:", _
      PrintToFile:= True, PrToFileName:="C:\test.ps"

   myPDF.FileToPDF "C:\test.ps", "C:\test.PDF", ""
End Sub

the only thing left is to delete postscript file after creation of pdf
file

Curtis