Vba printout

[Curtis]

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

 

[Curtis]

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

 

[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

 

[mojof1]

Hi Curtis,

Thanks for posting back after you figured it out. I"m trying to do the exact same thing. Although it produces a .ps file, it does not convert it to the .pdf.

I tried running your script in excel and it produced a .txt instead. the content is below

%%[ Error: syntaxerror; OffendingCommand: ) ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%


Would you be able to help me with figuring this out? thanks.